创建分区 USE[master] GO /**//**//**//****** Object: Database [Data Partition DB] Script Date: 10/08/2006 23:09:53 ******/ IFEXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB2') DROPDATABASE[Data Partition DB2] GO CREATEDATABASE[Data Partition DB2] ONPRIMARY (NAME='Data Partition DB Primary FG', FILENAME= 'C:Data2PrimaryData Partition DB Primary FG.mdf', SIZE=5, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG1] (NAME ='Data Partition DB FG1', FILENAME = 'C:Data2FG1Data Partition DB FG1.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG2] (NAME ='Data Partition DB FG2', FILENAME = 'C:Data2FG2Data Partition DB FG2.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG3] (NAME ='Data Partition DB FG3', FILENAME = 'C:Data2FG3Data Partition DB FG3.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 ), FILEGROUP [Data Partition DB FG4] (NAME ='Data Partition DB FG4', FILENAME = 'C:Data2FG4Data Partition DB FG4.ndf', SIZE = 5MB, MAXSIZE=500, FILEGROWTH=1 )
2,创建分区函数
use[Data Partition DB2] GO CREATE PARTITION FUNCTION[Data Partition Range](int) AS RANGE LEFTFORVALUES (100,200,300)
3,创建分区架构
USE[Data Partition DB2] go CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range] TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]);
4,创建表
USE[Data Partition DB2] go CREATETABLE MyTable (ID INTNOTNULL, Date DATETIME, Cost money) ON[Data Partition Scheme] (ID);
5,创建聚合索引
USE[Data Partition DB2] go CREATEUNIQUECLUSTEREDINDEX MyTable_IXC ON MyTable(ID) ON[Data Partition Scheme] (ID)
分区函数共有三个分临界值:100,200,300,有四个文件组[Data Partition DB FG1]到[... FG4],存储规则如下:
分区号
范围
文件组名
1
x<=100
[Data Partition DB FG1]
2
100<x<=200
[Data Partition DB FG2]
3
200<x<=300
[Data Partition DB FG3]
4
300<x
[Data Partition DB FG4]
现在要求加入500这个临界值,并加入文件组[Data Partition DB FG5],如下操作: 1,建立文件组:
ALTERDATABASE[Data Partition DB2]ADD FILEGROUP [Data Partition DB FG5] ALTERDATABASE[Data Partition DB2]ADDFILE(NAME='Data Partition DB FG5', FILENAME='D:DatabaseData Partition DB FG5.ndf') TO FILEGROUP [Data Partition DB FG5];
2,更改分区架构
ALTER PARTITION SCHEME [Data Partition Scheme] NEXT USED [Data Partition DB FG5];
3,更改分区函数
ALTER PARTITION FUNCTION[Data Partition Range](int) SPLIT RANGE (500)
然后我们来看有什么变化: 分区架构:
USE[Data Partition DB2] GO CREATE PARTITION SCHEME [Data Partition Scheme]AS PARTITION [Data Partition Range] TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3], [Data Partition DB FG5], [Data Partition DB FG4])
分区函数:
USE[Data Partition DB2] GO CREATE PARTITION FUNCTION[Data Partition Range](int) AS RANGE LEFTFORVALUES (100, 200, 300, 500)
USE[Data Partition DB2] GO CREATE PARTITION SCHEME [Data Partition Scheme]AS PARTITION [Data Partition Range] TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3], [Data Partition DB FG5], [Data Partition DB FG6], [Data Partition DB FG4])
分区函数:
USE[Data Partition DB2] GO CREATE PARTITION FUNCTION[Data Partition Range](int) AS RANGE LEFTFORVALUES (100, 200, 300,400, 500)
存储规则将变为
分区号
范围
文件组名
1
x<=100
[Data Partition DB FG1]
2
100<x<=200
[Data Partition DB FG2]
3
200<x<=300
[Data Partition DB FG3]
4
300<x<=400
[Data Partition DB FG5]
5
400<x<=500
[Data Partition DB FG6]
6
500<x
[Data Partition DB FG4]
所以对于具体时间维的数据仓库来说,时间做为临界点,是不断增加的,我们一开始创建事实表分区时,最后一个分区应该叫做类似[Data Partiotion DB FG LAST]的名称,