SQL SERVER 表及索引分区

表分区应该是处理海量数据一个最好的方法。名义上一个表，但分散存储于不同的物理文件上，这跟小表并没有多大区别。

不过，分区表的主键如何处理却不得不谨慎。

分区表的依据列最好就是主键。比如用户表，UserId是自动增长的标识列，主键，又以之作为划分分区的依据，每50万一个分区。在这里，主键与分区依据列完美结合，护发去头皮。

但有时候主键不一定是分区依据列。比如说，用户设置表。用户表与用户设置表是一对多的关系，一个用户有好多设置，即每个用户会有若干条设置记录。这种情况，UserId应该是分区依据列，但显然不能是主键，因为不唯一。

通常情况下，主键默认是聚集索引。而聚集索引是数据的一部分，就是说，聚集索引和表数据肯定是同一个文件组。如果主键不是分区依据列，又是聚集索引，这个表还怎么分区？我就试过，辛辛苦苦将一个巨大表分好区，此时尚未建主键，后来将主键建到一个独立的文件组，发现表数据也全部挪到该独立文件组，之前的分区努力化为泡影。

我的感觉是，这种情况下，主键不能定为聚集索引，应该做成非聚集索引。

除了依据列，其他索引不要建立在这个分区上（即所谓对齐），全部指定独立文件组，甚至自己分区。否则，系统会偷偷给你搭售依据列。

---------------------------------------------------------------------------------------

（以下内容抄自SQL SERVER 2008 联机帮助）

对聚集索引进行分区

对聚集索引进行分区时，聚集键必须包含分区依据列。对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下 SQL Server 将在聚集索引键列表中添加分区依据列。如果聚集索引是唯一的，则必须明确指定聚集索引键包含分区依据列。

 对非聚集索引进行分区

对唯一的非聚集索引进行分区时，索引键必须包含分区依据列。对非唯一的非聚集索引进行分区时，默认情况下 SQL Server 将分区依据列添加为索引的非键（包含性）列，以确保索引与基表对齐。如果索引中已经存在分区依据列，SQL Server 将不会向索引中添加分区依据列。

---------------------------------------------------------------------------------------

CREATE TABLE [dbo].[User_News_Log](
     [LogId] [int] IDENTITY(1,1) NOT NULL,
     [WebUserId] [int] NOT NULL,
     [Code] [varchar](20) NOT NULL,
     [Content] [varchar](700) NOT NULL,
     [CreateDate] [smalldatetime] NOT NULL,
--非聚集索引主键 
  CONSTRAINT [PK_user_news] PRIMARY KEY NonCLUSTERED 
 (
     [LogId] ASC  
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [UserNewsIndex]
 ) 
--分区表 
 ON UserNewsSCM(CreateDate)
--页级压缩 
 WITH 
 (
   DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 5)
 );
 GO      

也由此可知，想将现有未分区表转为分区表，有个方法就是将聚集索引建到分区上。运行完毕，你会欣喜地发现，表已经分区了。

附：一个现有巨大表分区、压缩、主键再分区的例子

--文件组 
 ALTER DATABASE [Compare] ADD FILEGROUP [Compare0]
 GO
 ALTER DATABASE [Compare] ADD FILEGROUP [Compare1]
 GO
 ALTER DATABASE [Compare] ADD FILEGROUP [Compare2]
 GO

-- 文件 
 ALTER DATABASE [Compare]
 ADD FILE 
 (NAME = N'Compare0',FILENAME = N'C:/Compare/Compare0.ndf',SIZE = 3MB,FILEGROWTH = 1MB)
 TO FILEGROUP [Compare0]
 GO

 ALTER DATABASE [Compare]
 ADD FILE 
 (NAME = N'Compare1',FILENAME = N'C:/Compare/Compare1.ndf',SIZE = 3MB,FILEGROWTH = 1MB)
 TO FILEGROUP [Compare1]
 GO

 ALTER DATABASE [Compare]
 ADD FILE 
 (NAME = N'Compare2',FILENAME = N'C:/Compare/Compare2.ndf',SIZE = 3MB,FILEGROWTH = 1MB)
 TO FILEGROUP [Compare2]
 GO-- 分区函数
 CREATE PARTITION FUNCTION ComparePFN(INT) AS RANGE LEFT FOR VALUES (50000,100000); 
 GO 
--分区方案 
 CREATE PARTITION SCHEME CompareSCM
 AS PARTITION ComparePFN
 TO (
 [Compare0]
 ,[Compare1]
 ,[Compare2]
 );
 GO 
--将聚集索引建到分区方案，数据自动跟着转移 
 CREATE CLUSTERED INDEX [cix_cp_wk] ON [dbo].[CatchMirror] 
 (
     [WebSiteKeyWordId] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON CompareSCM(WebSiteKeyWordId) 
 GO

-----------------------------------------------------------------
 -- 行压缩
 ----------------------------------------------------------------- 
 ALTER TABLE [CatchMirror]
 REBUILD PARTITION = ALL 
 WITH
 (
 DATA_COMPRESSION = ROW ON PARTITIONS(1 TO 3) 
 );
 GO

-------------------------------------------------------------------
 -- 分区索引（主键）
 ------------------------------------------------------------------- 
 ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex0]
 GO
 ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex1]
 GO
 ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex2]
 GO

 ALTER DATABASE [Compare]
 ADD FILE 
 (NAME = N'CompareIndex0',FILENAME = N'C:/Compare/CompareIndex0.ndf',SIZE = 3MB,FILEGROWTH = 1MB)
 TO FILEGROUP [CompareIndex0]
 GO

 ALTER DATABASE [Compare]
 ADD FILE 
 (NAME = N'CompareIndex1',FILENAME = N'C:/Compare/CompareIndex1.ndf',SIZE = 3MB,FILEGROWTH = 1MB)
 TO FILEGROUP [CompareIndex1]
 GO

 ALTER DATABASE [Compare]
 ADD FILE 
 (NAME = N'CompareIndex2',FILENAME = N'C:/Compare/CompareIndex2.ndf',SIZE = 3MB,FILEGROWTH = 1MB)
 TO FILEGROUP [CompareIndex2]
 GO

 CREATE PARTITION FUNCTION CompareIndexPFN(INT) AS RANGE LEFT FOR VALUES (200000000,400000000); 
 GO

 CREATE PARTITION SCHEME CompareIndexSCM
 AS PARTITION CompareIndexPFN
 TO (
 [CompareIndex0]
 ,[CompareIndex1]
 ,[CompareIndex2]
 );
 GO 
--主键，非聚集，分区，并启用行压缩 
 ALTER TABLE [dbo].[CatchMirror] ADD  CONSTRAINT [PK_CatchMirror] PRIMARY KEY NONCLUSTERED 
 (
     [CatchMirrorId] 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,DATA_COMPRESSION = ROW ) ON CompareIndexSCM(CatchMirrorId) 
 GO 
后记：
应用分区对性能的改善是显著的。未分区和压缩前，服务器硬盘队列达到30以上；分区和压缩后，硬盘队列10以下，足足改善了3倍。

补充一句：如果表已经应用了页级压缩，聚集索引就不要再应用行压缩，否则整个表又变成行压缩。
 
2、拆分分区： 
 
拆分分区，除非分区方案里 有多余的文件组，否则要先添加文件组，然后修改分区方案，再修改分区函数。 
 
 
 
ALTER PARTITION SCHEME UserSCM NEXT USED [user5];

 ALTER PARTITION FUNCTION UserPFN() SPLIT RANGE('5') 
 
3、分区依据列为字符型 
-- 分区函数 
 CREATE PARTITION FUNCTION UserPFN(VARCHAR(50)) AS RANGE LEFT FOR VALUES (
 '1','4','7','a','c','e','h','k','o','r','u','x','z');
 GO 
4、分区依据列为日期型 
-- 分区函数 
 CREATE PARTITION FUNCTION UserPFN(SMALLDATETIME) AS RANGE LEFT FOR VALUES ('2008-01-01','2009-01-01','2010-01-01','2011-01-01','2012-01-01');
 GO