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