SQL SERVER 分区表的总结
USE MASTERGO
--40万行分成5个文件组，PRIMARY加下面四个
--文件组命名:FG_数据库名_表名_字段名_流水号
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_Product_ID_1;
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_Product_ID_2;
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_Product_ID_3;
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_Product_ID_4;
GO
USE TEST
GO
--给每个文件组加个次数据文件
--文件命名:文件组名_data_流水号
ALTER DATABASE TEST
ADD FILE (
NAME=N'FG_TEST_Product_ID_1_data_1',
FILENAME=N'D:\Data\FG_TEST_Product_ID_1_data_1.ndf',
SIZE=50MB,
FILEGROWTH=10%
)
TO FILEGROUP FG_TEST_Product_ID_1;
ALTER DATABASE TEST
ADD FILE (
NAME=N'FG_TEST_Product_ID_2_data_1',
FILENAME=N'D:\Data\FG_TEST_Product_ID_2_data_1.ndf',
SIZE=50MB,
FILEGROWTH=10%
)
TO FILEGROUP FG_TEST_Product_ID_2;
ALTER DATABASE TEST
ADD FILE (
NAME=N'FG_TEST_Product_ID_3_data_1',
FILENAME=N'D:\Data\FG_TEST_Product_ID_3_data_1.ndf',
SIZE=50MB,
FILEGROWTH=10%
)
TO FILEGROUP FG_TEST_Product_ID_3;
ALTER DATABASE TEST
ADD FILE (
NAME=N'FG_TEST_Product_ID_4_data_1',
FILENAME=N'D:\Data\FG_TEST_Product_ID_4_data_1.ndf',
SIZE=50MB,
FILEGROWTH=10%
)
TO FILEGROUP FG_TEST_Product_ID_4;
GO
--创建分区函数
--分区函数命名：fn_Partition_表名_字段
CREATE PARTITION FUNCTION fn_Partition_Product_ID(INT)
AS
RANGE RIGHT
FOR VALUES(80000,160000,240000,320000);
GO
--创建分区架构
--分区架构命名：Sch_表名_字段名
CREATE PARTITION SCHEME Sch_Product_ID
AS PARTITION fn_Partition_Product_ID
TO
(,,,,);
GO
/**一切准备工作都已经完成，但是我用的是一个生产环境中的库的附本，
所以这个表上除了主键索引，还有一些其它的索引。
接下来的操作，主题就是在一个现有的表上如何进行分区。
**/
USE
GO
BEGIN TRANSACTION
--移除聚集主键
ALTER TABLE . DROP CONSTRAINT
--重新添加聚集主键，并指定分区架构
ALTER TABLE . ADDCONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON)
ON ()
/**
以下是对这个表上其它的非聚集索引的处理：重建时指定分区架构。
在WITH子句中加入DROP_EXISTING = ON选项，重建时如存在同名索引，先删除后新建。
**/
/**这样做目的其实就是实现存储位置对齐:不同索引会按照相同的分区架构将数据分布到文件组.
但是个人认为,这样好处是在于多表的存储对齐而不是单表.
详情可参考:http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx
**/
CREATE NONCLUSTERED INDEX ON .
(
ASC
)
INCLUDE ( )
WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON)
ON ()
CREATE NONCLUSTERED INDEX ON .
(
ASC,
ASC
)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON)
ON ()
CREATE NONCLUSTERED INDEX ON .
(
ASC,
ASC,
ASC
)
WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON)
ON ()
CREATE NONCLUSTERED INDEX ON .
(
ASC,
ASC,
ASC
)
INCLUDE ( ,
)
WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON)
ON ()
COMMIT TRANSACTION
页:
[1]