环境:
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 --查询分区函数