前文

本系列文章

• 資料庫索引深入淺出(一)
• 資料庫索引深入淺出(二)

兩種基本索引

• Clustered Index(叢集索引)
• NonClustered Index(非叢集索引)

兩種Lookup(如果NonClustered Index無法滿足查詢結果執行)

• RID Lookup
• Key Lookup

本篇會介紹其他種類Index

Covering Index

我們先來看看Covering Index語法.

最主要使在NONCLUSTERED INDEX後面加上INCLUDE欄位.

CREATE NONCLUSTERED INDEX IX_T_Id_Convering on dbo.T(
	id
) INCLUDE (
	UserId,
	UserGroup
)

加入INCLUDE欄位含意

在NONCLUSTERED INDEX把Column加入INCLUDE區域後此NONCLUSTERED INDEX會把此欄位資料加入至子頁層.之後如果要查找資料時就不用在Lookup回去

所以我們可以把Covering Index當作是偽CLUSTERED INDEX.

如果每次只需要SELECT少部分欄位且範圍較大又須排序，Covering Index執行效率會比CLUSTERED INDEX來的快.

適合Covering Index很適合用在查出來Column不需要當作Key

案例解說

樣本資料一樣使用上一篇的資料

SELECT *
FROM dbo.T
WHERE id = 10000

SELECT *
FROM dbo.T with(index(IX_T_Id))
WHERE id = 10000

有兩段語法一段是有使用Hint，執行出來後會有兩個執行計畫.

第一個執行計畫是上面的語法,第二個執行計畫是下面的語法

建立完Convering Index後我們使用的查詢就會變成只使用Seek，而且在執行成本也大幅降低.

Filter Index

Filter Index語法就是在最後寫where條件

CREATE NONCLUSTERED INDEX FIX_T_Id_UserGroup on  dbo.T(
	id
) INCLUDE (
	UserId,
	UserGroup
)
where UserGroup = 8

上面語法意思是只針對於UserGroup = 8的Row建立資料在子頁層，Filter Index主要是提升維護性和降低Index大小.

注意:如果有使用到Filter Index的SP或Script，如果沒有加上SET QUOTED_IDENTIFIER ON就會造成錯誤，所以在撰寫Script時要養成加上上面語法的好習慣.

Index Intersection

SQL-Server可透過多個Index完成一段查詢(通常選擇子集合較小)在透過JOIN完成查詢

假如我們有兩個一個查詢會用到UserId和Id Column當作條件我們可能會建立下面這個索引.

CREATE CLUSTERED INDEX IX_T_UserId_Id on dbo.T(
	UserId,
	Id
)

但除了同時利用UserId和Id Column當作條件外還可能個別當作查詢條件.

我們就可以考慮把這個Index拆開成兩個，這樣可以提高索引使用率（因為執行計畫透過統計值來產生，而Index統計值計算是由Index第一個Column來當計算）

注意:把Index拆成兩個也許可以增加查詢效率，但每個Index就是一個B+ Tree這會造成維護上成本

CREATE CLUSTERED INDEX IX_T_UserId on dbo.T(
	UserId
)

CREATE CLUSTERED INDEX IX_T_Id on  dbo.T(
	Id
)

Primary Key

在SQL-Server很常使用PRIMARY KEY但你知道他代表甚麼含意嗎?

PRIMARY KEY是也是一個Index，他可以設定NonClustered Index或是Clustered Index

PRIMARY KEY有幾個特徵

1. 資料不能重複(Unique)
2. Columns都必須定義成NOT NULL
3. PRIMARY KEY是一個Index

預設建立的PRIMARY KEY是Clustered Index，但我們使用語法自行建立為NonClustered Index的PRIMARY KEY

如下範例我們可以建立一個NONCLUSTERED的PRIMARY KEY

CREATE TABLE T(
    ID INT NOT NULL,  
    CONSTRAINT [PK_T] PRIMARY KEY NONCLUSTERED (
        ID
    )
)