1.SQL
alter database Test add filegroup Before20170628
alter database Test add filegroup T2018
alter database Test add file
(Name=N'Before20170628',filename='D:GroupFileBefore20170628.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb)
to filegroup Before20170628
alter database Test add file
(Name=N'T2018',filename='D:GroupFileT2018.ndf',size=5mb,maxsize=100Mb,filegrowth=5mb)
to filegroup T2018
---1 创建分区规则
create partition function RangeTime (datetime)
as range left for values ('2017-06-28')
---2
create partition scheme RangeSchema_CreateTime
as partition RangeTime
to (Before20170628,T2018)
--创建表
create table Shop
(
ID varchar(50),
ShopName varchar(50),
CreateTime datetime
) on RangeSchema_CreateTime(CreateTime)
----插入测试数据
declare @in int=1
while @in<1000000
begin
insert into shop values(NEWID(),'love网络'+Cast(rand()*1000 as varchar(20)),
DateADD(day,Cast(rand()*2000 as int),'2017-01-01'))
set @in=@in+1
end
---查询各文件组 记录数量
select $partition.rangeTime(createTime) as number,count(*) as rcount
from shop group by $partition.rangeTime(createTime)
select count(*) from shop
2.可通过窗口创建分区
