几句SQL 完成分区使用

/****** Script for SelectTopNRows command from SSMS ******/

– 创建表
SELECT TOP 1000 [Id]
,[Name]
,[CreateTime]
FROM [demo].[dbo].[Users]

– 添加文件组
alter database [demo] add filegroup T2018
alter database [demo] add filegroup T2019
alter database [demo] add filegroup T2020

–添加文件
alter database [demo] add file
(Name=N’T2018’,filename=‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\T2018.ndf’,size=5mb,maxsize=100Mb,filegrowth=5mb)
to filegroup T2018

alter database [demo] add file
(Name=N’T2019’,filename=‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\T2019.ndf’,size=5mb,maxsize=100Mb,filegrowth=5mb)
to filegroup T2019

alter database [demo] add file
(Name=N’T2020’,filename=‘C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\T2020.ndf’,size=5mb,maxsize=100Mb,filegrowth=5mb)
to filegroup T2020

–创建分区函数
create partition function RangeTime (datetime)
as range left for values (‘2018-12-31’,‘2019-12-31’)

–创建分区方案
create partition scheme RangeSchema_CreateTime
as partition RangeTime
to (T2018,T2019,T2020)

–每个时间段写入数据
INSERT INTO [dbo].[Users]
([Name]
,[CreateTime])
VALUES
(‘Tom3’
,‘2020-01-01’)
GO

select * from [dbo].[Users]

–删除表存在的聚集索引
ALTER TABLE Users DROP CONSTRAINT PK__Users__3214EC078EA2153D

–修改为非聚集索引
ALTER TABLE Users ADD CONSTRAINT PK_Users PRIMARY KEY NONCLUSTERED (Id ASC)

–创建分区聚集索引
CREATE CLUSTERED INDEX IX_CreateTime ON Users ( CreateTime )
ON RangeSchema_CreateTime ( CreateTime )

–查询每个分区的数据条数
SELECT $partition.RangeTime(CreateTime), count(*) FROM Users group by $partition.RangeTime(CreateTime)

–查询分区=1 上面的所有数据
select * from Users where $partition.RangeTime(CreateTime) =1