12 規劃與建立索引 12-1 索引的基礎 12-2 資料表的索引規劃 12-3 SQL Server自動建立的索引

Presentation on theme: "12 規劃與建立索引 12-1 索引的基礎 12-2 資料表的索引規劃 12-3 SQL Server自動建立的索引"— Presentation transcript:

1

2 12 規劃與建立索引 12-1 索引的基礎 12-2 資料表的索引規劃 12-3 SQL Server自動建立的索引
12-4 建立資料表的索引 12-5 修改、重建與刪除索引 12-6 檢視SQL Server的執行計劃 12-7 建立檢視表與計算欄位的索引 12-8 篩選索引與資料行存放區索引

3 12-1 索引的基礎 12-1-1 索引簡介 12-1-2 索引的種類 12-1-3 M路搜尋樹與B樹
SQL Server的索引結構

4 索引簡介-說明 索引（Index）可以幫助資料庫引擎在磁碟中定位記錄資料，以便在資料表的龐大資料中加速找到資料。換句話說，建立資料表的索引可以提昇SQL查詢效率，讓我們更快取得資料庫的查詢結果。 在資料表建立索引需要額外的參考資料，資料庫管理系統可以將資料表的部分欄位資料預先進行排序，此欄位稱為「索引欄位」（Index Columns），索引欄位值稱為鍵值（Key Value）。

5 索引簡介-圖例 一般來說，索引資料包含兩個欄位值：一為索引欄位；一為指標（Pointer）欄位，它是指向對應到資料表記錄位置的值，如下圖所示：

6 索引的種類-主索引 主索引（Primary Index）就是將資料表的主索引鍵建立成索引，一個資料表只能擁有一個主索引。在資料表建立主索引的索引欄位，欄位值一定不能重覆，即欄位值是唯一，而且不允許是空值（NULL）。 在主索引的索引欄位可以是一個或多個欄位的組合，如果是由多個資料表欄位所組合，稱為複合索引（Composite Index）或結合索引（Concatenated Index），在主索引的複合索引中，個別欄位允許重複值，但是整個組合值仍然需要是唯一值。

7 索引的種類 唯一索引（Unique Index）的欄位值也是唯一的，不同於主索引只能有一個，在一個資料表可以擁有多個唯一索引，這也是與主索引唯一的差別。 一般索引（Regular Index）的索引欄位值並不需要是唯一的，其主要目的是加速資料表的搜尋與排序。在一個資料表可以擁有多個一般索引。換句話說，我們可以在資料表選擇一些欄位來建立一般索引，其主要目的就是在增進查詢效能。

8 12-1-3 M路搜尋樹與B樹-說明 B樹（B-Trees）是資料結構的一種樹狀搜尋結構，它是擴充自二元搜尋樹的一種平衡的M路搜尋樹。
M路搜尋樹（M-way Search Trees）是指樹的每一個節點都擁有至多M個子樹和M-1個鍵值，鍵值是以遞增方式由小至大來排序，其節點結構如下圖所示：

9 12-1-3 M路搜尋樹與B樹- M路搜尋樹(圖例)
例如：四路搜尋樹的每一個節點最多有3鍵值和4個子樹，如下圖所示：

10 M路搜尋樹與B樹-B樹 (說明) B樹（B-Tree）屬於一種樹狀搜尋結構，它是擴充自二元搜尋樹的一種平衡的M路搜尋樹。M為B樹的度數（Order），由Bayer和McCreight提出的一種平衡的M路搜尋樹，其定義如下所示： B樹的每一個節點最多擁有M個子樹。 B樹根節點和葉節點之外的中間節點，至少擁有ceil(M/2)個子節點，ceil()函數可以大於等於參數的最小整數，例如：ceil(4) = 4、ceil(4.33) = 5、ceil(1.89) = 2和ceil(5.01) = 6。 B樹的根節點可以少於2個子節點。葉節點至少擁有ceil(M/2) - 1個鍵值。 B樹的所有葉節點都位在樹最底層的同一階層（Level），換句話說，從根節點開始走訪到各葉節點所經過的節點數都相同，它是一棵相當平衡的樹狀搜尋結構。

11 M路搜尋樹與B樹-B樹 (圖例) 例如：一棵度數5的B樹，所有中間節點至少擁有ceil(5/2) = 3個子節點（即至少2個鍵值），最多5個子節點（4個鍵值），葉節點至少擁有2個鍵值，最多為4個鍵值，如下圖所示：

12 SQL Server的索引結構-說明 SQL Server索引結構（Index Organization）是組成索引分頁的方法，可以分為叢集索引和非叢集索引兩種。 在SQL Server資料表只能擁有一個叢集索引，通常就是主索引，主索引的索引欄位可以是單一欄位，或多欄位的複合索引。 在一個資料表可以擁有多個非叢集索引，它可以是唯一索引或一般索引，當然也可以是多索引欄位的複合索引。

13 12-1-4 SQL Server的索引結構- 叢集索引
叢集索引（Clustered Indexes）是一種B樹結構，當SQL Server資料表建立叢集索引後，資料表的記錄資料會依叢集索引欄位的鍵值來排序，如下圖所示：

14 12-1-4 SQL Server的索引結構- 非叢集索引(說明)
非叢集索引（Nonclustered Indexes）是一種類似叢集索引的B樹結構，其差異在於資料表的記錄並不會依據非叢集索引的鍵值來排序，而且非叢集索引的葉節點是索引分頁，並不是資料分頁。 非叢集索引葉節點的索引分頁內容是非叢集索引鍵值，和指向資料表記錄的記錄定位（Row Locator）指標。在叢集資料表建立非叢集索引，因為資料表本身已經擁有叢集索引，所以葉節點的索引分頁中，記錄定位值是對應的叢集索引鍵值。

15 12-1-4 SQL Server的索引結構- 非叢集索引(圖例)

16 12-2 資料表的索引規劃 索引的優缺點 建立索引的注意事項 選擇索引欄位

17 索引的優缺點 索引的優點：索引可以加速資料存取，因為不用一筆一筆比較來搜尋記錄，資料庫引擎可以透過索引結構來快速找到指定記錄，它能夠讓SQL語言的合併查詢、排序和群組操作更加的有效率。 索引的缺點：在資料表建立索引需要額外的磁碟空間和維護成本，因為資料表在插入、更新和刪除記錄時，資料庫引擎需要花費額外時間和資源來更新索引資料。

18 12-2-2 建立索引的注意事項- 建立索引的限制條件
因為資料表的記錄資料是使用叢集索引的順序來排列，所以SQL Server資料庫的每一個資料表只能建立一個叢集索引，但是可以在資料表的多個欄位建立多個非叢集索引。 在一個資料表最多只能有一個叢集索引和249個非叢集索引。 複合索引欄位數最多只能有16個欄位。 單一索引欄位或複合索引欄位的總長度需在900位元組以內，而且不能替ntext、text和image資料類型的欄位建立索引。

19 12-2-2 建立索引的注意事項- 如何建立複合索引
複合索引是指索引欄位超過一個的索引，我們可以選擇資料表的多個欄位集合來建立複合索引。一般來說，在資料表應該儘量避免建立複合索引，而是以多個單一欄位索引來取代，因為複合索引的索引欄位尺寸通常比較大，需要更多的磁碟讀取，進而影響整體的執行效能。 不只如此，SQL Server複合索引在使用上有一些限制，只有當SELECT指令的WHERE子句使用第1個欄位進行查詢時，才會使用複合索引來增加查詢效率。

20 選擇索引欄位- 應該作為索引的欄位 對於資料表中查詢頻繁的欄位，我們應該替這些欄位建立索引，例如：主鍵、外來鍵、經常需要合併查詢的欄位、排序欄位和需要查詢指定範圍的欄位。 一般來說，資料表的主鍵建議建立叢集索引（SQL Server預設會自動建立），其他欄位建立成非叢集索引。

21 選擇索引欄位- 不應該作為索引的欄位 對於資料表查詢時很少參考到的欄位、大量重複值欄位（例如：欄位值只有男或女）或bit等資料類型的欄位，就不應該替它們建立索引。

22 12-3 SQL Server自動建立的索引 PRIMARY KEY欄位的索引 UNIQUE欄位的索引

23 12-3 SQL Server自動建立的索引 當在SQL Server資料庫建立資料表時，資料表指定為PRIMARY KEY或UNIQUE的欄位，SQL Server都會自動替這些欄位建立索引。

24 PRIMARY KEY欄位的索引 在建立資料表時指定為PRIMARY KEY的欄位（即主索引鍵或稱為主鍵），SQL Server預設將它自動建立成叢集索引，換句話說，資料表的記錄是使用主索引鍵欄位值來排列。 例如：【學生】資料表指定【學號】欄位的主索引鍵，預設就會建立此欄位的叢集索引。

25 UNIQUE欄位的索引-說明 在建立資料表時指定為UNIQUE的欄位，SQL Server都會預設自動建立成非叢集索引，其型別是唯一索引鍵，表示欄位值需要是唯一。

26 12-3-2 UNIQUE欄位的索引-範例 SQL指令碼檔：Ch12_3_2.sql
CREATE TABLE 熱銷產品 ( 產品編號 char(5) NOT NULL PRIMARY KEY , 產品名稱 varchar(30) UNIQUE , 定價 money )

27 12-4 建立資料表的索引 使用Management Studio建立索引 使用T-SQL指令建立索引

28 12-4-1 使用Management Studio建立 索引-選擇索引欄位

29 12-4-1 使用Management Studio建立 索引-選擇內含欄位
接著我們可以加入索引包含的欄位，請選上方【包含的資料行】標籤，按【加入】鈕新增索引包含的欄位，可以看到勾選資料表欄位的對話方塊。如下圖所示：

30 12-4-1 使用Management Studio建立 索引-內含欄位說明
在資料表建立非叢集索引時，建議新增內含欄位，以便讓SQL Server能夠最佳化索引的使用。例如：查詢學生生日的SELECT指令，如下所示： SELECT 生日 FROM 學生 WHERE 姓名 = '陳會安' 上述SELECT指令可以使用【姓名_索引】的非叢集索引，因為有在索引包含【生日】欄位。 如果沒有包含【生日】欄位且沒有【生日】欄位的索引，SQL Server仍然會使用主索引的叢集索引來進行搜尋，而不會使用【姓名_索引】的非叢集索引。

31 12-4-2 使用T-SQL指令建立索引-語法 在T-SQL語言是使用CREATE INDEX指令建立資料表的索引，其基本語法如下所示：
CREATE [ UNIQUE ] [ CLUSTERED ] INDEX 索引名稱 ON 資料表名稱 (欄位名稱[(長度)][ ASC | DESC ][,..n] ) [ INCLUDE (欄位清單) ] [ WITH 索引選項 ] [ ON filegroup_name ]

32 12-4-2 使用T-SQL指令建立索引-索引選項
在WITH子句可以指定索引選項，如果有多個，請使用「,」逗號分隔。常用的索引選項說明，如下表所示： 索引選項 說明 PAD_INDEX 索引頁預留空間 FILLFACTOR = x 填滿因數 IGNORE_DUP_KEY 忽略重複值 STATISTICS_NORECOMPUTE 不重新計算統計資料 DROP_EXISTING 重建存在的索引，即卸除目前的索引後，重新建立

33 12-4-2 使用T-SQL指令建立索引-範例1 SQL指令碼檔：Ch12_4_2_01.sql
在【教務系統】資料庫的【員工】資料表新增【姓名】欄位的非叢集索引【員工姓名_索引】，索引包含【電話】和【薪水】欄位，如下所示： CREATE INDEX 員工姓名_索引 ON 員工(姓名) INCLUDE (電話, 薪水)

34 12-4-2 使用T-SQL指令建立索引-範例2 SQL指令碼檔：Ch12_4_2_02.sql
在【教務系統】資料庫的【課程】資料表新增【名稱】和【學分】欄位的非叢集索引【名稱學分_索引】，這是一個唯一的複合索引，如下所示： CREATE UNIQUE INDEX 名稱學分_索引 ON 課程(名稱, 學分)

35 12-5 修改、重建與刪除索引 12-5-1 使用Management Studio修改與重建資料表的索引
使用T-SQL指令修改與重建索引 刪除資料表的索引

36 12-5-1 使用Management Studio修改與 重建資料表的索引-方法1
在「物件總管」視窗【員工姓名_索引】上，執行【右】鍵快顯功能表的【屬性】指令，可以看到「索引屬性」對話方塊來修改索引。

37 12-5-1 使用Management Studio修改與 重建資料表的索引-方法2
在修改資料表欄位定義資料時，執行「資料表設計工具>索引/索引鍵」指令，開啟「索引/索引鍵」對話方塊來修改索引，如下圖所示：

38 12-5-1 使用Management Studio修改與 重建資料表的索引-重建索引

39 12-5-2 使用T-SQL指令修改與重建索引- 修改索引(說明)
T-SQL語言修改索引仍然是使用CREATE INDEX指令，只是加上DROP_EXISTING選項。 在CREATE INDEX指令建立索引時，如果加上DROP_EXISTING選項，表示我們準備建立新索引來取代同名的存在索引。請注意！當加上DROP_EXISTING選項時，一定需要存在同名的索引，如果索引不存在，執行時就會產生錯誤。

40 12-5-2 使用T-SQL指令修改與重建索引- 修改索引(範例)
SQL指令碼檔：Ch12_5_2_01.sql 在【教務系統】資料庫的【員工】資料表修改【員工姓名_索引】索引，將它改為唯一索引、加上IGNORE_DUP_KEY選項和新增包含【城市】欄位，如下所示： CREATE UNIQUE INDEX 員工姓名_索引 ON 員工(姓名) INCLUDE (電話, 薪水, 城市) WITH IGNORE_DUP_KEY, DROP_EXISTING

41 12-5-2 使用T-SQL指令修改與重建索引- 重建索引(語法)
T-SQL的ALTER INDEX指令可以停用、重建索引、重組索引或設定索引選項來修改存在的索引，其基本語法如下所示： ALTER INDEX 索引名稱 [ALL] ON 資料表名稱 [ REBUILD [WITH (索引選項清單)] | REORGANIZE | DISABLE | SET (索引選項清單)]

42 12-5-2 使用T-SQL指令修改與重建索引- 重建索引(範例1)
SQL指令碼檔：Ch12_5_2_02.sql 重建【教務系統】資料庫【員工】資料表的所有索引，並且將填滿因數改為80%，如下所示： ALTER INDEX ALL ON 員工 REBUILD WITH (FILLFACTOR = 80)

43 12-5-2 使用T-SQL指令修改與重建索引- 重建索引(範例2)
SQL指令碼檔：Ch12_5_2_03.sql 在【教務系統】資料庫停用【員工】資料表的【員工姓名_索引】索引，如下所示： ALTER INDEX 員工姓名_索引 ON 員工 DISABLE

44 12-5-3 刪除資料表的索引- 使用Management Studio
我們也可以在「索引/索引鍵」對話方塊刪除索引，請在左邊選取索引後，按下方的【刪除】鈕來刪除資料表的索引。

45 12-5-3 刪除資料表的索引-使用T-SQL指令
T-SQL語言的DROP INDEX指令可以刪除資料表的索引，其基本語法如下所示： DROP INDEX 資料表名稱1.索引名稱1 [, 資料表名稱2.索引名稱2, …] SQL指令碼檔：Ch12_5_3.sql 在【教務系統】資料庫刪除【員工】資料表的【員工姓名_索引】索引，如下所示： DROP INDEX 員工.員工姓名_索引

46 12-6 檢視SQL Server的執行計劃-說明
在Management Studio工具可以使用圖形化方式來顯示SQL Server執行計劃，執行計劃是SQL Server查詢最佳化模組選擇的資料擷取方法，我們可以透過檢視執行計劃來了解查詢特性，並且幫助我們進行查詢最佳化。 Management Studio的估計執行計劃並不會真的執行T-SQL查詢或批次。不過，此估計執行計劃仍有可能是資料庫引擎最後使用的執行計劃。

47 12-6 檢視SQL Server的執行計劃-圖例

48 12-7 建立檢視表與計算欄位的索引 建立計算欄位的索引 建立檢視表索引

49 建立計算欄位的索引-說明 在資料表新增計算欄位就是為了建立計算欄位的索引，例如：查詢【估價單】資料表平均單價在100和200元之間的記錄資料，如下所示： SELECT 產品編號 FROM 估價單 WHERE (總價 / 數量) BETWEEN AND 在WHERE子句條件因為擁有運算式，當【估價單】資料表的記錄資料十分龐大時，我們就需要在資料表新增計算欄位【平均單價】（即SQL指令碼：Ch7_4_1.sql），然後建立此計算欄位的索引來增進查詢效率。

50 建立計算欄位的索引-需求條件1 擁有權需求（Ownership Requirements）：計算欄位與資料表必須是同一位擁有者，即計算欄位的運算式不能使用其他資料表的欄位。 決定性需求（Determinism Requirements）：計算欄位的值是由運算式的欄位值決定，只需輸入的欄位值相同，就會輸出相同的結果。 精確性需求（Precision Requirements）：計算欄位的運算式結果需要是精確的（Precise），也就是沒有使用float或real型別的欄位。

51 CONCAT_NULL_YIELDS_NULL
建立計算欄位的索引-需求條件2 資料類型需求（Data Type Requirements）：計算欄位的運算式結果不可以是text、ntext和image型別，不過，組成欄位仍然可以使用這些型別。 SQL選項需求（SQL Option Requirements）：在建立時需要使用SET指令指定一些SQL選項。 SQL選項 設定值 NUMERIC_ROUNDABORT OFF ANSI_NULLS ON ANSI_PADDING ANSI_WARNINGS ARITHABORT CONCAT_NULL_YIELDS_NULL QUOTED_IDENTIFIER

52 12-7-1 建立計算欄位的索引- 建立計算欄位的索引(範例1)
SQL指令碼檔：Ch12_7_1_01.sql 在【教務系統】資料庫的【估價單】資料表，建立計算欄位【平均單價】的【平均單價_索引】索引，如下所示： SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF

53 12-7-1 建立計算欄位的索引- 建立計算欄位的索引(範例2)
GO CREATE NONCLUSTERED INDEX 平均單價_索引 ON 估價單(平均單價 ASC) INCLUDE (產品編號)

54 建立檢視表索引-說明 檢視表索引（Indexed View）是指擁有唯一叢集索引的檢視表，在SQL Server不只可以替一般資料表建立索引，就連虛擬資料表的檢視表也可以建立索引，其最主要目的是增進檢視表的查詢效率，特別針對大量資料的合併和統計摘要檢視表，如下所示： 合併檢視表：多資料表執行大量資料的合併查詢。 統計摘要檢視表：使用群組和聚合函數建立大量資料的統計查詢。

55 12-7-2 建立檢視表索引- 檢視表索引的限制條件1
需要是檢視表的擁有者，才有權限執行CREATE INDEX指令建立檢視表的索引。 檢視表索引欄位需要是是精確的（Precise），也就是沒有使用float或real型別的欄位。 檢視表需要先建立唯一叢集索引後，才能建立其他非叢集索引。 如果建立檢視表的SELECT指令包含GROUP BY子句，在建立唯一叢集索引時，就只能選擇GROUP BY子句的欄位。

56 12-7-2 建立檢視表索引- 檢視表索引的限制條件2
和建立計算欄位的索引相同，在建立檢視表索引前，我們需要設定SQL選項：ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER為ON，SET NUMERIC_ROUNDABORT選項為OFF。 在建立好唯一叢集索引後，對於基底資料表執行INSERT、UPDATE和DELETE指令或查詢時，都需要設定前述7個SQL選項。

57 12-7-2 建立檢視表索引- 基底資料表的限制條件
對於檢視表資料來源的基底資料表來說，在使用CREATE TABLE指令建立資料表時，SQL選項ANSI_NULLS需設為ON。

58 建立檢視表索引- 檢視表的限制條件1 在使用CREATE VIEW建立檢視表時，SQL選項ANSI_NULLS和QUOTED_IDENTIFIER需為ON。 建立檢視表時需要使用WITH SCHEMABINDING選項。 檢視表的資料來源只能是同一個資料庫的資料表，而不能是檢視表，而且它們是同一位擁有者。 在檢視表的所有欄位都需是決定性欄位，不能包含text、ntext和image型別。至於參考的資料表和自訂函數名稱都需使用二段式名稱，例如：dbo.員工，也就是包含結構描述。

59 12-7-2 建立檢視表索引- 檢視表的限制條件2 SELECT指令不可使用的指令，如下所示：
SELECT子句不可以使用「*」，一定需要指明欄位名稱，而且不能使用TOP和DISTINCT關鍵字。 FROM子句不可使用子查詢的衍生資料表（Derived Tables）。也不能使用OUTER JOIN、UNION。 GROUP BY子句不可使用HAVING子句、CUBE和ROLLUP。 不可以使用ORDER BY和COMPUTE BY子句。 只能使用SUM()和COUNT_BIG()聚合函數（與COUNT()函數相同，只是傳回bigint資料類型），而且SUM()函數的欄位不能是NULL空值。 不能使用資料集函數，例如：OPENROWSET()，和不可以使用全文檢索搜尋的CONTAINS和FREETEXT。

60 12-7-2 建立檢視表索引- 建立檢視表(範例1) SQL指令碼檔：Ch12_7_2_01.sql
在【教務系統】資料庫建立名為【學生上課教室_檢視】的合併檢視表，可以顯示學生在各教室的上課數，如下所示： SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF GO

61 12-7-2 建立檢視表索引- 建立檢視表(範例2) CREATE VIEW dbo.學生上課教室_檢視
WITH SCHEMABINDING AS SELECT 學生.學號, 班級.教室,COUNT_BIG(*) AS 上課數 FROM dbo.學生 INNER JOIN dbo.班級 ON 學生.學號 = 班級.學號 GROUP BY 學生.學號, 班級.教室 GO SELECT * FROM 學生上課教室_檢視

62 12-7-2 建立檢視表索引- 建立唯一叢集索引(範例)
SQL指令碼檔：Ch12_7_2_02.sql 在【教務系統】資料庫替【學生上課教室_檢視】檢視表，建立名為【上課報表_索引】的唯一叢集索引，如下所示： CREATE UNIQUE CLUSTERED INDEX 上課報表_索引 ON 學生上課教室_檢視(學號, 教室)

63 12-7-2 建立檢視表索引- 建立非叢集索引(範例)
SQL指令碼檔：Ch12_7_2_03.sql 在【教務系統】資料庫替【學生上課教室_檢視】檢視表，建立名為【教室_索引】的非叢集索引，如下所示： CREATE NONCLUSTERED INDEX 教室_索引 ON 學生上課教室_檢視(教室) INCLUDE (學號, 上課數)

64 建立檢視表索引- 檢視估計執行計劃 請在Management Studio分別開啟SQL指令碼檔案Ch12_7_2_04.sql和Ch12_7_2_05.sql後，當我們檢視估計執行計劃，即可看到查詢最佳化模組，使用檢視表索引來執行SELECT指令。

65 12-8 篩選索引與資料行存放區索引 篩選索引 資料行存放區索引 叢集資料行存放區索引

66 篩選索引-說明 篩選索引（Filtered Index）是一種擁有條件的索引，換句話說，SQL Server並不會將索引欄位的所有記錄都建立索引，而是只有哪些符合條件的記錄才會建立索引。 在CREATE INDEX指令是使用WHERE子句來指定建立篩選索引的篩選條件。實務上，篩選索引最常是使用在建立疏鬆欄位的索引。

67 12-8-1 篩選索引-範例1 SQL指令碼檔：Ch12_8_1.sql
在【教務系統】資料庫建立【廠商名單】資料表，內含疏鬆欄位【分公司數】，然後建立名為【分公司數_索引】的篩選索引，如下所示： CREATE TABLE 廠商名單 ( 廠商編號 int NOT NULL IDENTITY PRIMARY KEY, 廠商名稱 varchar(100), 廠商類型 tinyint NOT NULL, 分公司數 int SPARSE )

68 12-8-1 篩選索引-範例2 GO CREATE NONCLUSTERED INDEX 分公司數_索引 ON 廠商名單(分公司數)
WHERE 廠商類型 = 3

69 資料行存放區索引-說明 「資料行存放區索引」（Columnstore Index，全名為非叢集資料行存放區索引）是針對大型資料倉儲新增以欄位為基礎（Column-based）的索引，基本上，資料行存放區索引不是使用紀錄為單位來儲存，而是改用欄位為單位來儲存，如下圖所示：

70 資料行存放區索引-提升查詢效率 因為資料行存放區索引是使用VertiPaq壓縮技術，可以在記憶體中儲存龐大的壓縮資料來減少I/O和搜尋時間。例如：以生日為條件的SQL查詢，如下所示： SELECT 姓名, 電話 FROM 學生 WHERE 生日=' ' 上述SELECT指令的WHERE子句是使用生日為條件，資料行存放區索引只需載入生日欄位的資料頁，不用載入整筆記錄的資料頁，因為資料量大幅減少，可以大幅提升資料查詢效率。

71 12-8-2 資料行存放區索引-注意事項 一個SQL Server資料表只能有一個資料行存放區索引。
資料行存放區索引不能使用篩選條件，也不能使用INCLUDE關鍵字。 資料行存放區索引的索引欄位不可是計算欄位。 資料行存放區索引的資料表成為唯讀資料表。 資料行存放區索引的索引欄位不支援binary、varbinary、ntext、text、image、varchar(max)、nvarchar(max)、uniqueidentifier、rowversion、timestamp、sql_variant、超過18位數的decimal和numeric資料類型。

72 12-8-2 資料行存放區索引-使用Management Studio建立
請在「物件總管」視窗展開資料表【課程備份】 ，在【索引】上執行【右】鍵快顯功能表的「新增索引>非叢集資料行存放區索引」指令，可以看到「新增索引」對話方塊。

73 12-8-2 資料行存放區索引- 使用T-SQL建立(語法)
T-SQL語言建立資料行存放區索引的語法和非叢集索引相似，只是改為COLUMNSTORE關鍵字，其語法如下所示： CREATE COLUMNSTORE INDEX 索引名稱 ON 資料表名稱 (欄位名稱1,欄位名稱2,… 欄位名稱N) 上述語法使用COLUMNSTORE關鍵字建立資料行存放區索引，在括號中是建立索引的欄位清單。

74 12-8-2 資料行存放區索引- 使用T-SQL建立(範例)
SQL指令碼檔：Ch12_8_2_02.sql 請在【學生備份】資料表建立名為【學生資料行_索引】的資料行存放區索引，索引欄位有姓名、生日和電話，如下所示： CREATE COLUMNSTORE INDEX 學生資料行_索引 ON 學生備份 (姓名, 生日, 電話)

75 12-8-3 叢集資料行存放區索引-說明 在第12-8-2節預設建立的是非叢集資料行存放區索引，這是使用在唯讀查詢，而且無法更新索引。
SQL Server也可以建立叢集資料行存放區索引，這是一種可以執行DML指令的資料行存放區索引，我們可以在沒有叢集索引、唯一、主鍵和外來鍵條件約束的資料表建立叢集資料行存放區索引。

76 12-8-3 叢集資料行存放區索引-使用Management Studio建立
請在「物件總管」視窗展開【課程備份2】資料表，在【索引】上執行【右】鍵快顯功能表的「新增索引>叢集資料行存放區索引」指令，可以看到「新增索引」對話方塊。

77 12-8-3 叢集資料行存放區索引-使用T-SQL指令建立（語法）
T-SQL語言建立叢集資料行存放區索引的語法和叢集索引相似，只是加上COLUMNSTORE關鍵字，其語法如下所示： CREATE CLUSTERED COLUMNSTORE INDEX 索引名稱 ON 資料表名稱 上述語法在COLUMNSTORE關鍵字之前加上CLUSTERED，就可以建立叢集資料行存放區索引。如果沒有加上CLUSTERED，或使用NONCLUSTERED，就是建立第12-8-2節的非叢集資料行存放區索引，其語法如下所示： CREATE NONCLUSTERED COLUMNSTORE INDEX 索引名稱 ON 資料表名稱 (欄位名稱1,欄位名稱2,… 欄位名稱N)

78 12-8-3 叢集資料行存放區索引-使用T-SQL指令建立（範例）
請執行Ch12_8_3_01.sql建立【學生備份2】資料表，筆者準備在此資料表使用T-SQL指令來建立叢集資料行存放區索引。 SQL指令碼檔：Ch12_8_3_02.sql 請在【學生備份2】資料表建立名為【學生資料行_叢集索引】的叢集資料行存放區索引，如下所示： CREATE CLUSTERED COLUMNSTORE INDEX 學生資料行_叢集索引 ON 學生備份2

79 12-8-3 叢集資料行存放區索引-使用T-SQL指令建立（圖例）
在成功執行SQL指令後，我們可以在Management Studio的「物件總管」視窗看到建立的叢集資料行存放區索引，如下圖所示：