SQL SERVER 分区表的总结

benzhou

USE MASTER　　
GO
　　
--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
　　
([PRIMARY],[FG_TEST_Product_ID_1],[FG_TEST_Product_ID_2],[FG_TEST_Product_ID_3],[FG_TEST_Product_ID_4]);
　　
GO
　　

　　
/**一切准备工作都已经完成，但是我用的是一个生产环境中的库的附本，
　　
所以这个表上除了主键索引，还有一些其它的索引。
　　
接下来的操作，主题就是在一个现有的表上如何进行分区。
　　
**/
　　
USE [TEST]
　　
GO
　　
BEGIN TRANSACTION
　　
--移除聚集主键
　　
ALTER TABLE [dbo].[Product] DROP CONSTRAINT [PK_Product]
　　

　　
--重新添加聚集主键，并指定分区架构
　　
ALTER TABLE [dbo].[Product] ADD  CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
　　
(
　　
    [ID] 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 [Sch_Product_ID]([ID])
　　

　　
/**
　　
以下是对这个表上其它的非聚集索引的处理：重建时指定分区架构。
　　
在WITH子句中加入DROP_EXISTING = ON选项，重建时如存在同名索引，先删除后新建。
　　
**/
　　
/**这样做目的其实就是实现存储位置对齐:不同索引会按照相同的分区架构将数据分布到文件组.
　　
   但是个人认为,这样好处是在于多表的存储对齐而不是单表.
　　
   详情可参考:http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx
　　
**/
　　
CREATE NONCLUSTERED INDEX [IX_Product_CategoryID_ID] ON [dbo].[Product]
　　
(
　　
    [CategoryID] ASC
　　
)
　　
INCLUDE ( [ID])
　　
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 [Sch_Product_ID]([ID])
　　

　　
CREATE NONCLUSTERED INDEX [IX_Product_ComID_IsAudit] ON [dbo].[Product]
　　
(
　　
    [ComID] ASC,
　　
    [IsAudit] 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 [Sch_Product_ID]([ID])
　　

　　
CREATE NONCLUSTERED INDEX [IX_Product_ComID_IsShelf_ShelfDate] ON [dbo].[Product]
　　
(
　　
    [ComID] ASC,
　　
    [IsShelf] ASC,
　　
    [ShelfDate] 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 [Sch_Product_ID]([ID])
　　

　　
CREATE NONCLUSTERED INDEX [IX_Product_IA_IS_SD_ID_CID] ON [dbo].[Product]
　　
(
　　
    [IsAudit] ASC,
　　
    [IsShelf] ASC,
　　
    [ShelfDate] ASC
　　
)
　　
INCLUDE ( [ID],
　　
[CategoryID])
　　
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 [Sch_Product_ID]([ID])
　　

　　
COMMIT TRANSACTION