SQL Server 2008 创建分区表

--- 创建文件组


alter database test add filegroup Before2013
alter database test add filegroup T2013
alter database test add filegroup T2014
alter database test add filegroup After2014


--- 创建次文件


alter database test add file (Name='Before2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\Before2013.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup Before2013
alter database test add file (Name='T2013',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\T2013.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup T2013
alter database test add file (Name='T2014',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\T2014.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup T2014
alter database test add file (Name='After2014',filename='D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL08\MSSQL\DATA\After2014.ndf',size=5mb,maxsize=100mb,filegrowth=5mb) to filegroup After2014


--- 创建分区函数


create partition function RangeTime(datetime)
as range left for values('2012-12-31','2013-12-31','2014-12-31')


--- 创建分区方案


create partition scheme RangeScheme_CreateTime
as partition RangeTime
to (Before2013,T2013,T2014,After2014)


--- 创建表
create table Shop 
(
ID varchar(50),
ShopName varchar(50),
CreateTime datetime
) on RangeScheme_CreateTime(CreateTime)


--- 插入十万条数据


declare @i int 
set @i =1
while @i<90000
begin
insert into Shop values(NEWID(),'love热卖'+CAST(RAND()*1000 as varchar(20)),DATEADD(DAY,CAST(RAND()*2000 as int),'2012-1-1'))
set @i=@i+1
end


--- 统计每个分区的记录数
select $partition.RangeTime(CreateTime) as number ,COUNT(*) as rcount
from Shop group by $partition.RangeTime(CreateTime)