19.2 SQL Server将现有表分区

 

SQL Server将现有表分区

 

目录

• SQL Server将现有表分区
  • 简介
  • 示例
    • 1）创建文件组
    • 2）创建分区函数
    • 3）创建分区方案
    • 4）在分区列上创建聚集索引

 

简介

步骤如下：

• 创建文件组
• 创建分区函数
• 创建分区方案
• 根据分区方案在表上创建聚集索引。

示例

此示例根据年份对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;

 

分类: SQL Server

标签: SQL Server , SQL Server高级 , 数据库