19.2 SQL Server将现有表分区
SQL Server将现有表分区
简介
步骤如下：
- 创建文件组
- 创建分区函数
- 创建分区方案
- 根据分区方案在表上创建聚集索引。
示例
此示例根据年份对sales.orders表进行分区
1）创建文件组
首先，创建两个新的文件组，存储2016年和2017年订单日期的行：
ALTER DATABASE bikestores
ADD FILEGROUP salesorders_2016;
ALTER DATABASE bikestores
ADD FILEGROUP salesorders_2017;
然后，将文件组映射到物理文件。注意，在执行以下语句之前，需要在服务器中提前创建D:\data文件夹：
ALTER DATABASE bikestores
ADD FILE (
NAME = salesorders_2016,
FILENAME = 'D:\data\salesorders_2016.ndf',
SIZE = 10 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP salesorders_2016;
ALTER DATABASE bikestores
ADD FILE (
NAME = salesorders_2017,
FILENAME = 'D:\data\salesorders_2017.ndf',
SIZE = 10 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP salesorders_2017;
2）创建分区函数
创建一个接受日期并返回三个分区的分区函数：
CREATE PARTITION FUNCTION sales_order_by_year_function(date)
AS RANGE LEFT
FOR VALUES ('2016-12-31', '2017-12-31')
3）创建分区方案
基于sales_order_by_year_function分区函数创建分区方案：
CREATE PARTITION SCHEME sales_order_by_year_scheme
AS PARTITION sales_order_by_year_function
TO ([salesorders_2016], [salesorders_2017], [primary]);
4）在分区列上创建聚集索引
订单表将order_id作为主键。此主键列也包含在聚集索引中。
要按order_date列对订单表进行分区，需要在分区方案sales_order_by_year_scheme上为order_date列创建聚集索引。
但是聚集索引只能有一个，所以需要将order_id列的聚集索引更改为非聚集索引，才可以在order_date列上创建新聚集索引。
但是order_id由order_items表中的外键引用。因此，需要执行以下步骤：
首先，从order_items表中删除外键order_id：
ALTER TABLE [sales].[order_items]
DROP CONSTRAINT [FK__order_ite__order__3A81B327]
注意，你数据库中的约束名称FK__order_ite__order __3A81B327可能不同。
然后，从订单表中删除主键约束：
ALTER TABLE [sales].[orders]
DROP CONSTRAINT [PK__orders__46596229EDE70106];
第三步，在primary分区上添加order_id为非聚集主键：
ALTER TABLE [sales].[orders]
ADD PRIMARY KEY NONCLUSTERED([order_id] ASC)
ON [PRIMARY];
第四步，在表的分区方案上把order_date列添加到聚集索引：
CREATE CLUSTERED INDEX ix_order_date
ON [sales].[orders]
(
[order_date]
) ON [sales_order_by_year_scheme]([order_date])
第五步，删除表上的聚集索引：
DROP INDEX ix_order_date
ON [sales].[orders];
最后，将外键约束添加回order_items表：
ALTER TABLE [sales].[order_items]
WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders] ([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE;
最好在事务中运行上面的所有语句：
BEGIN TRANSACTION;
ALTER TABLE [sales].[order_items]
DROP CONSTRAINT [FK__order_ite__order__3A81B327];
ALTER TABLE [sales].[orders]
DROP CONSTRAINT [PK__orders__46596229EDE70106];
ALTER TABLE [sales].[orders] ADD PRIMARY KEY NONCLUSTERED
(
[order_id] ASC
) ON [PRIMARY];
CREATE CLUSTERED INDEX ix_order_date
ON [sales].[orders]
(
[order_date]
) ON [sales_order_by_year_scheme]([order_date]);
DROP INDEX ix_order_date
ON [sales].[orders];
ALTER TABLE [sales].[order_items]
WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders] ([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE;
COMMIT TRANSACTION;
要检查每个分区中的行数，请使用以下查询：
SELECT
p.partition_number AS partition_number,
f.name AS file_group,
p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'orders'
order by partition_number;
