简单易懂的MS sql 2005 表达式分区
By weisnet On
2010-07-19
At
10:06 View:68
首先建立文件组：
|
SQL |
- alter database TestDB add filegroup fg1;
- alter database TestDB add filegroup fg2;
- alter database TestDB add filegroup fg3;
- alter database TestDB add filegroup fg4;
|
|
SQL |
- alter DATABASE [TestDB] add file
- ( NAME = 'fg1', FILENAME = N'D:\sqldata\fg1.ndf') to FILEGROUP [fg1];
- alter DATABASE [TestDB] add file
- ( NAME = 'fg2', FILENAME = N'D:\sqldata\fg2.ndf') to FILEGROUP [fg2];
- alter DATABASE [TestDB] add file
- ( NAME = 'fg3', FILENAME = N'D:\sqldata\fg3.ndf') to FILEGROUP [fg3];
- alter DATABASE [TestDB] add file
- ( NAME = 'fg4', FILENAME = N'D:\sqldata\fg4.ndf') to FILEGROUP [fg4];
- go
|
建立分区函数：
|
SQL |
- create partition function partifunc(int)
- as range right for values (250000,500000,750000);
|
建立分区方案：
|
SQL |
- create partition scheme test_scheme
- as partition [partifunc] to (fg1,fg2,fg3,fg4)
- go
|
建立一个新表在test_scheme方案上
|
SQL |
- create table Partitbl
- (
- pid int identity(1,1) not null,
- pname varchar(50) not null,
- classid int
- ) on test_scheme (classid);
|
写入一些1000000数据：
|
SQL |
- declare
- @nid int;
- begin
- set @nid="1;"
- while (@nid <= 1000000)
- begin
- insert into Partitbl(pname,classid) values('PName'+str(@nid),@nid);
- set @nid = @nid+1;
- end
- end
|
查询第四分区数据：
|
SQL |
- select * from partitbl where $partition.partifunc(classid) = 4 /*第四分区*/
|
新建一个分区：
|
SQL |
- alter database TestDB add fileGroup fg5;
- alter database TestDB add file (NAME = 'fg5',filename="'d:\sqldata\fg5.ndf') to filegroup fg5;
- -- next used fg5
- alter partition scheme test_scheme
- next used fg5
- go
- alter partition function partifunc()
- split range(1000000)
- go
|
写入数据并查询第五分区:
|
SQL |
- declare
- @nid int;
- begin
- set @nid="1000001;"
- while (@nid <= 1250000)
- begin
- insert into Partitbl(pname,classid) values('PName'+str(@nid),@nid);
- set @nid = @nid+1;
- end
- end
- select * from partitbl where $partition.partifunc(classid) = 5
|