1 create database test; 2 use test; 3 4 --创建文件组，对文件进行分类管理 5 alter database test add filegroup Before2013; 6 alter database test add filegroup T2013; 7 alter database test add filegroup T2014; 8 alter database test add filegroup After2014; 9 10 --创建文件，并且添加到对应的文件组 11 alter database test add file (Name=N'Before2013',filename='E:sqlserverBefore20131.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup Before2013; 12 alter database test add file (Name=N'T2013',filename='E:sqlserverT20131.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup T2013; 13 alter database test add file (Name=N'T2014',filename='E:sqlserverT20141.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup T2014; 14 alter database test add file (Name=N'After2014',filename='E:sqlserverAfter20141.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb) to filegroup After2014; 15 16 --编写分区函数，以时间为分区依据 17 create partition function RangeTime(datetime) as range left for values('2012-12-31','2013-12-31','2014-12-31'); 18 19 --编写分区方案，与文件组一一对应 20 create partition scheme RangeScheme_createTime as partition RangeTime to (Before2013,T2013,T2014,After2014); 21 22 --创建分区表,主键最后再定义 23 create table shop( 24 id varchar(50) not null , 25 name varchar(50), 26 createTime datetime 27 )on RangeScheme_createTime(createTime); 28 29 --插入测试数据 30 insert into shop values(NEWID(),'test1','2011-04-05'); 31 insert into shop values(NEWID(),'test2','2013-04-15'); 32 insert into shop values(NEWID(),'test2','2013-04-15'); 33 insert into shop values(NEWID(),'test3','2014-04-25'); 34 insert into shop values(NEWID(),'test3','2014-04-25'); 35 insert into shop values(NEWID(),'test3','2014-04-25'); 36 insert into shop values(NEWID(),'test4','2015-04-05'); 37 38 select * from shop; 39 40 --统计每个分区的数据量 41 select $partition.RangeTime(createTime) as number,COUNT(*) as rcount from shop group by $partition.RangeTime(createTime) 42 --number rcount 43 --1 1 44 --2 2 45 --3 3 46 --4 1 47 48 --判断某个时间在哪个分区 49 select $partition.[RangeTime]('2014-12-2') as '分区' 50 --分区 51 --3 52 53 --查找3分区的数据 54 select * from shop where $partition.Rangetime(createTime)=3 55 --id name createTime 56 --26A7DBBA-5A3D-43BC-81A9-68311EB724C3 test3 2014-04-25 00:00:00.000 57 --E5D53AB5-DE33-451E-82B9-A5B4268AE5CB test3 2014-04-25 00:00:00.000 58 --51CABE36-FEBC-41F0-B9AA-A2CF72853220 test3 2014-04-25 00:00:00.000