具有內含資料行的索引

使用內含資料行避免大小限制

您可以在非叢集索引中包含非索引鍵之索引資料行，以避免超出目前索引大小限制 (最大 16 個索引鍵資料行，最大 900 個位元組索引鍵大小)計算索引鍵資料行數或索引鍵大小時，Database Engine 不會考慮非索引鍵之索引資料行。

例如，假設在AdventureWorks2008R2 範例資料庫中，您要建立 Document 資料表中下列資料行的索引：

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

由於 nchar 和 nvarchar 資料類型的每個字元都需要 2 個位元組，因此包含這三個資料行的索引可能會比 900 個位元組的大小限制多出 10 個位元組 (455 * 2)。使用 CREATE INDEX 陳述式的 INCLUDE 子句，索引鍵可定義為 (Title, Revision)，而 FileName 則定義為非索引鍵之索引資料行。這樣，索引鍵大小會是 110 個位元組 (55 * 2)，且索引仍能包含所有必須的資料行。下列陳述式會建立這類索引。

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Document_Title 
ON Production.Document (Title, Revision) 
INCLUDE (FileName); 

內含資料行索引指導方針

設計具有內含資料行的非叢集索引時，請考慮下列指導方針：

• 非索引鍵之索引資料行是定義於 CREATE INDEX 陳述式的 INCLUDE 子句。

• 非索引鍵之索引資料行僅能定義於資料表或索引檢視的非叢集索引上。

• 除了 text、ntext 與 image，允許所有資料類型。

• 具決定性之精確或非精確的計算資料行都可以當做內含資料行。如需詳細資訊，請參閱＜在計算資料行上建立索引＞。

• 如同索引鍵資料行，只要計算資料行資料類型可以作為無索引鍵索引資料行，則從 image、ntext 與 text 衍生的計算資料行即可以是非索引鍵之索引資料行。

• 資料行名稱無法同時指定於 INCLUDE 清單與索引鍵資料行清單兩者中。

• 資料行名稱在 INCLUDE 清單中不得重複。

資料行大小指導方針

• 至少必須定義一個索引鍵資料行。非索引鍵之索引資料行數目的上限為 1023 個資料行。這是資料表資料行數目的上限減 1。

• 索引鍵資料行 (不包含非索引鍵之索引資料行) 必須遵守現有索引大小的限制 (上限為 16 個索引鍵資料行)，且索引鍵總大小為 900 個位元組。

• 所有非索引鍵之索引資料行大小總計僅由 INCLUDE 子句中指定的資料行大小限定；例如，varchar(max) 資料行是限定為 2 GB。

資料行修改指導方針

當您修改定義為內含資料行的資料表資料行時，則下列限制適用：

• 必須先卸除索引，才能從資料表卸除非索引鍵之索引資料行。

• 除非執行下列動作，否則無法變更非索引鍵之索引資料行：

  • 將資料行的 Null 屬性從 NOT NULL 變更為 NULL。

  • 增加 varchar、nvarchar 或 varbinary 資料行的長度。

    [!附註]

    這些資料行修改限制也適用索引鍵資料行。

設計建議

重新設計具有大型索引鍵大小的非叢集索引，如此僅有用於搜尋與查閱的資料行才會是索引鍵資料行。讓涵蓋查詢的所有其他資料行都作為內含非索引鍵之索引資料行。如此一來，您將擁有涵蓋查詢所需的所有資料行，但是索引鍵本身會變得很小而且很有效率。

例如，假設您要設計能夠涵蓋下列查詢的索引。

USE AdventureWorks2008R2;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

若要涵蓋查詢，必須在索引中定義每個資料行。雖然您可以將所有資料行定義為索引鍵資料行，但是索引鍵大小應是 334 個位元組。由於只有實際作為搜尋條件的資料行才是 PostalCode 資料行，且長度為 30 個位元組，所以較佳的索引設計方式應該是將 PostalCode 定義為索引鍵資料行，並將所有其他的資料行作為非索引鍵之索引資料行包含在內。

下列陳述式會建立具有內含資料行的索引，可以涵蓋查詢。

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

效能考量因素

避免加入不必要的資料行。加入過多的索引資料行、索引鍵或無索引鍵，可能會發生以下的效能問題：

• 頁面上可以放入的索引資料列變少。這將使得 I/O 的作業增加而降低快取的效率。

• 必須有更多磁碟空間才能儲存索引。尤其是，加入 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 資料類型作為非索引鍵之索引資料行，將大幅增加磁碟空間的需求。這是因為資料行的值複製到索引的分葉層級。因此，它們會同時存在於索引與基底資料表中。

• 維護索引時，會增加修改、插入、更新或刪除基礎資料表或索引檢視的時間。

您必須決定，提高查詢效能，與修改資料時對效能的影響和需要額外磁碟空間，兩者熟輕熟重。如需評估查詢效能的詳細資訊，請參閱＜查詢微調＞。

請參閱

概念