1、创建分区函数

点击(此处)折叠或打开

2、创建分区架构

点击(此处)折叠或打开

3、创建分区表

点击(此处)折叠或打开

4、查询各个分区

点击(此处)折叠或打开

SELECT S.name schemename, f.name functionname, P.partition_number, DS.name filegroupname, V.value, P.rows
FROM SYS.indexes I
INNER JOIN SYS.partition_schemes S ON S.data_space_id=I.data_space_id
INNER JOIN SYS.destination_data_spaces DDS ON DDS.partition_scheme_id=S.data_space_id
INNER JOIN SYS.data_spaces DS ON DS.data_space_id=DDS.data_space_id
INNER 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
INNER JOIN SYS.partition_functions F ON S.function_id=F.function_id
LEFT JOIN SYS.partition_range_values V ON F.function_id=V.function_id AND V.boundary_id=P.partition_number-F.boundary_value_on_right
WHERE I.object_id=object_id('CWFX2')
--各个分区的条数
SELECT $PARTITION.FnP(ysdate) p_num, COUNT(1) rows FROM CWFX2 GROUP BY $PARTITION.FnP(ysdate)

5、分区表的分割
分割从老分区中复制到新分区，在从老分区中删除

点击(此处)折叠或打开

6、分区的合并
合并向没有边界值的一边合并

点击(此处)折叠或打开

7、把一个表改成分区表脚本
图形化的右键要分区的表-->存储-->创建分区。

点击(此处)折叠或打开

BEGIN TRANSACTION
CREATE PARTITION FUNCTION [FnCwfx](datetime) AS RANGE LEFT FOR VALUES (N'2010-01-01T00:00:00', N'2015-01-01T00:00:00', N'2018-01-01T00:00:00')
CREATE PARTITION SCHEME [ScCwfx] AS PARTITION [FnCwfx] TO ([PRIMARY], [FileGroup1], [FileGroup2], [PRIMARY])
CREATE CLUSTERED INDEX [ClusteredIndex_on_ScCwfx_636070443000552980] ON [dbo].[Cwfx]
(
[YsDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ScCwfx]([YsDate])
DROP INDEX [ClusteredIndex_on_ScCwfx_636070443000552980] ON [dbo].[Cwfx]
COMMIT TRANSACTION

ITPub博客

Sql Server分区表