Sqlserver 创建基于时间的表分区简单用法

环境：

select @@version

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)
    Nov 30 2018 12:57:58
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

测试过程：

-- 创建文件组,文件
alter database t add filegroup ByDatatimeGroup1
alter database t add filegroup ByDatatimeGroup2
alter database t add filegroup ByDatatimeGroup3
alter database t add filegroup ByDatatimeGroup4

alter database t add file 
(name=N'ByDatatime1',filename=N'/var/opt/mssql/data/ByDatatime1.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByDatatimeGroup1
alter database t add file 
(name=N'ByDatatime2',filename=N'/var/opt/mssql/data/ByDatatime2.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByDatatimeGroup2
alter database t add file 
(name=N'ByDatatime3',filename=N'/var/opt/mssql/data/ByDatatime3.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByDatatimeGroup3
alter database t add file 
(name=N'ByDatatime4',filename=N'/var/opt/mssql/data/ByDatatime4.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByDatatimeGroup4


-- 分区函数
create partition function [PF_GK] (datetime) as range left for values
('2019-01-01','2019-02-01','2019-03-01','2019-04-01')

-- 编写分区方案
 create partition scheme RangeSchema_CreateTime
 as partition PF_GK
 to (ByDatatimeGroup1,ByDatatimeGroup2,ByDatatimeGroup3,ByDatatimeGroup4,ByDatatimeGroup4)

-- 创建分区表
  create table T_TEST
 (
   ID  varchar(50),
   Name varchar(50),
   CreateTime datetime 
 ) on RangeSchema_CreateTime(CreateTime);

-- test
insert into t_test(id,name,createtime) values(1,'china','2018-12-01')
insert into t_test(id,name,createtime) values(1,'china','2019-01-01')
insert into t_test(id,name,createtime) values(2,'japan','2019-02-01')
insert into t_test(id,name,createtime) values(1,'usa','2019-03-01')
insert into t_test(id,name,createtime) values(1,'france','2019-04-01')
insert into t_test(id,name,createtime) values(1,'france','2019-05-01')
insert into t_test(id,name,createtime) values(1,'france','2019-06-01')

--查看分区表中，每个非空分区存在的行数
select $partition.PF_GK(CreateTime) as pNum,count(*) as rCount
from T_TEST
group by  $partition.PF_GK(CreateTime)

-- 查看指定分区中的数据记录
select * from T_TEST where $partition.PF_GK(CreateTime)=5
select * from sys.partition_schemes -- 查询分区
select * from sys.partition_range_values -- 查询分区范围
select * from sys.partition_functions --查询分区函数

 

官方链接：https://docs.microsoft.com/zh-cn/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=sql-server-2017