sqlserver 表分区-datetime 时间字段分区案例

1，创建文件组

USE [master]
GO
ALTER DATABASE [test] ADD FILEGROUP [datetime1]
GO
ALTER DATABASE [test] ADD FILEGROUP [datetime2]
GO
ALTER DATABASE [test] ADD FILEGROUP [datetime3]
GO
ALTER DATABASE [test] ADD FILEGROUP [datetime4]
GO
ALTER DATABASE [test] ADD FILEGROUP [datetime5]
GO

USE [master]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N't4_1', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\t4_1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [datetime1]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N't4_2', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\t4_2.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [datetime2]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N't4_3', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\t4_3.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [datetime3]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N't4_4', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\t4_4.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [datetime4]
GO
ALTER DATABASE [test] ADD FILE ( NAME = N't4_5', FILENAME = N'D:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test\t4_5.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [datetime5]
GO
如果要删除数据文件到文件组可以使用
alter database test remove file  test01

2,创建分区函数
 刚才也说了，我们是按照时间进行切分的，将数据表数据分成
 1,2018 年
 2,2019 年
 3,2020 年
 4,2021 年
 5,2022 年以后

create partition function [PF_card] (datetime) as range left for values
('2018-01-01','2019-01-01','2020-01-01','2021-01-01')

如果要删除分区函数
drop partition function [PF_card]

3，有了分区函数后，需要指定不同分区的数据放到哪个文件组中
CREATE PARTITION SCHEME [PS_card] AS PARTITION [PF_card]
TO ([PRIMARY], [datetime1], [datetime2], [datetime3], [datetime4])

从上面的sql，我们可以看到三个点将时间轴分成了4段
第一：PF_card 为分组函数名。

第二：left 其实就是当时间点在边界时到底属于左侧还是右侧，因为这里是left，所以属于左侧，如果是right关键词，那就属于右侧了。

4：编写分区方案
 分区方案也就是将分区函数与文件组进行一个关联，刚才也说了，4个时间点将一个时间轴分成了4部分，刚好对应了5个文件组。

那么具体的sql写法如下：

 create partition scheme RangeSchema_CreateTime
 as partition PF_card
 to (datetime1,datetime2,datetime3,datetime4)

5：创建分区表
跟普通表创建有点不一样，分区表的创建还需要指定这个分区需要使用哪个分区方案下的分区字段，那么这里就是RangeSchema_CreateTime

中的CreateTime字段。
 create table Shop
 (
   ID  varchar(50),
   ShopName varchar(50),
   CreateTime datetime 
 ) on RangeSchema_CreateTime(CreateTime)

这里要注意，如果在创建表的时候指定了ID为主键的话，这个时候需要指定ID为分区字段，否则会报错的。
这时候可以在不要主键的情况下先创建表，然后再指定ID为主键。

6：插入测试数据并统计

　　

    这里我先插入10w条数据，然后来看看数据在各个分区的情况。
?declare @i int
?set @i = 1
?
?while @i < 100000
?begin insert into equipment_n values(@i,dateadd(dd,datediff(dd,'2012-01-01','2015-01-30')*RAND(),'2012-01-01'),1,@i+1,@i+2,@i+3)
?set @i = @i +1
?end

7,统计每个分区的数据量

      这里主要有一个查询分区的关键字“$partition”，非常的有用
 select $partition.RangeTime(operaDate) as number, COUNT(*) as count
 from equipment_n group by $partition.RangeTime(operaDate)

好了，到这个我们通过sql语句来实现分区表就已经完成了