SQL Serve已分區索引的特殊指導原則（1）- 索引對齊

一、前言

在MSDN上看到一篇關於SQL Server 表分區的文檔：已分區索引的特殊指導原則，如果你對表分區沒有實戰經驗的話是比較難理解文檔裡面描述的意思。這裡我就裡面的一些概念進行講解，方便大家的交流。

二、解讀

“索引要與其基表對齊，並不需要與基表參與相同的命名分區函數。但是，索引和基表的分區函數在實質上必須相同，即：

1) 分區函數的參數具有相同的數據類型；

2) 分區函數定義了相同數目的分區；

3) 分區函數為分區定義了相同的邊界值。”

下面我們進行測試：

--1.創建文件組
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_01]
    
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_02]
    
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_Id_03]
    
--2.創建文件
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\DataBase\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_01];
    
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\DataBase\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_02];
    
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_03_data',FILENAME = N'E:\DataBase\FG_TestUnique_Id_03_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_Id_03];
    
--3.創建分區函數
CREATE PARTITION FUNCTION
Fun_TestUnique_Id(INT) AS
RANGE RIGHT
FOR VALUES(10000000,20000000)
    
--4.創建分區方案
CREATE PARTITION SCHEME
Sch_TestUnique_Id AS
PARTITION Fun_TestUnique_Id
TO([FG_TestUnique_Id_01],[FG_TestUnique_Id_02],[FG_TestUnique_Id_03])

上面的SQL腳本創建了分區函數：Fun_TestUnique_Id(INT)和分區方案：[Sch_TestUnique_Id]。下面我們創建類似的分區函數：Fun_TestUnique_SiteId(INT)和分區方案：[Sch_TestUnique_SiteId]。這兩個函數完全符合上面提到的3個條件：

1) 分區函數的參數具有相同的數據類型；（都是Int類型）

2) 分區函數定義了相同數目的分區；（都是3個分區）

3) 分區函數為分區定義了相同的邊界值。”（邊界值都是10000000,20000000）

--1.創建文件組
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_SiteId_01]
    
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_SiteId_02]
    
ALTER DATABASE [Test]
ADD FILEGROUP [FG_TestUnique_SiteId_03]
    
--2.創建文件
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_SiteId_01_data',FILENAME = N'E:\DataBase\FG_TestUnique_SiteId_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_SiteId_01];
    
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_SiteId_02_data',FILENAME = N'E:\DataBase\FG_TestUnique_SiteId_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_SiteId_02];
    
ALTER DATABASE [Test]
ADD FILE
(NAME = N'FG_TestUnique_SiteId_03_data',FILENAME = N'E:\DataBase\FG_TestUnique_SiteId_03_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_TestUnique_SiteId_03];
    
--3.創建分區函數
CREATE PARTITION FUNCTION
Fun_TestUnique_SiteId(INT) AS
RANGE RIGHT
FOR VALUES(10000000,20000000)
    
--4.創建分區方案
CREATE PARTITION SCHEME
Sch_TestUnique_SiteId AS
PARTITION Fun_TestUnique_SiteId
TO([FG_TestUnique_SiteId_01],[FG_TestUnique_SiteId_02],[FG_TestUnique_SiteId_03])

接下來創建一個以這個分區方案進行分區的表[TestUnique]；這個表的聚集索引是創建在分區方案：[Sch_TestUnique_Id]上的。接著創建一個唯一索引：[IX_TestUnique_SiteIdUrl]是創建在分區方案[Sch_TestUnique_SiteId]上的。那麼這個唯一索引跟基表是對齊的嘛？

--5.創建分區表
CREATE TABLE [dbo].[TestUnique](
    [Id] [int] IDENTITY(600000000,1) NOT FOR REPLICATION NOT NULL,
    [SiteId] [int] NULL,
    [Url] [nvarchar](420) NULL,
    [PublishOn] [datetime] NULL,
    [AddOn] [datetime] NULL,
 CONSTRAINT [PK_Archive] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [Sch_TestUnique_Id]([Id])
) ON [Sch_TestUnique_Id]([Id])
GO
    
--6.創建唯一索引
CREATE NONCLUSTERED INDEX [IX_TestUnique_SiteIdUrl] ON [dbo].[TestUnique] 
(
    [SiteId] ASC,
    [Url] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [Sch_TestUnique_SiteId]([SiteId])
GO