sqlserver创建分区表

最新推荐文章于 2024-10-18 08:38:46 发布

Z~先生

最新推荐文章于 2024-10-18 08:38:46 发布

阅读量745

点赞数

本文链接：https://blog.csdn.net/haisen11/article/details/115455903

版权

要做分区表首先要创建好需要的文件和文件组 --创建分区表的步骤分为5步：

–　　（1）创建数据库文件组

–　　（2）创建数据库文件

–　　（3）创建分区函数

–　　（4）创建分区方案

–　　（5）创建分区表

创建分区函数

CREATE PARTITION FUNCTION partition_function_name ( DATETIME  )
AS RANGE RIGHT
FOR VALUES ( '2016-01-01','2016-02-01','2016-03-01','2016-04-01','2016-05-01',
'2016-06-01','2016-07-01','2016-08-01','2016-09-01','2016-10-01','2016-11-01',
'2016-12-01','2017-01-01','2017-02-01','2017-03-01','2017-04-01','2017-05-01'
,'2017-06-01','2017-07-01','2017-08-01','2017-09-01','2017-10-01','2017-11-01'
,'2017-12-01','2018-01-01','2018-02-01','2018-03-01','2018-04-01','2018-05-01'
,'2018-06-01','2018-07-01','2018-08-01','2018-09-01','2018-10-01','2018-11-01'
,'2018-12-01')

查询分区函数
SELECT * FROM sys.partition_functions

创建分区方案

CREATE PARTITION SCHEME Scheme_DateTime
AS PARTITION partition_function_name
TO ( month201601, month201602, month201603, month201604, month201605,month201606,
month201607,month201608,month201609,month201610,month201611,month201612,month201701,
month201702,month201703,month201704,month201705,month201706,month201707,month201708,
month201709,month201710,month201711,month201712,month201801,month201802,month201803,
month201804,month201805,month201806,month201807,month201808,month201809,month201810,
month201811,month201812,month201901)

查询分区方案
SELECT * FROM sys.partition_schemes

创建分区表

CREATE TABLE [dbo].[tDeviceStatusHistry333](
	[id] [int] NOT NULL,
	[deviceId] [int] NOT NULL,
	[value] [varchar](20) NULL,
	[insertTime] [datetime] NOT NULL
) ON Scheme_DateTime ( insertTime )

插入数据测试

insert into tDeviceStatusHistry333 select * from 
tDeviceStatusHistry where insertTime between '2017-08-01' and '2018-02-01'

select count(0) from tDeviceStatusHistry where insertTime between '2017-08-01' and '2018-02-01'

查询分区使用状况

select
      convert(varchar(50), ps.name) as partition_scheme,
      p.partition_number,
      ds2.name as filegroup,
      convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
      str(p.rows, 9) as rows,ps.function_id
from sys.indexes i
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id
join sys.partition_functions pf on ps.function_id = pf.function_id
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right
--WHERE i.object_id = object_id('PartitionedTable')
--and i.index_id in (0, 1)
order by partition_scheme,p.partition_number;

添加文件和文件组，修改分区函数和分区方案

--ALTER DATABASE [Ivy] ADD FILEGROUP month202106;

--ALTER DATABASE [Ivy] ADD FILE ( NAME = N'DH202106', FILENAME = N'D:\Data\DH202106.ndf' , SIZE = 128MB , FILEGROWTH = 1024KB ) TO FILEGROUP [month202106];

--alter partition scheme Scheme_DateTime next used [month202106];

--alter partition function  partition_function_name() split range('2021-06-01')