Share via


具有內含資料行的索引

在 SQL Server 2005 中,您可以加入無索引鍵資料行至非叢集索引的分葉層級,以擴充非叢集索引的功能。由於包含無索引鍵資料行,因此您可以建立涵蓋更多查詢的非叢集索引。這是因為無索引鍵資料行有下列好處:

  • 與索引鍵資料行一樣,它們可以是不允許的資料類型。
  • 計算索引鍵資料行數或索引鍵大小時,Database Engine 不會考慮它們。

查詢中所有的資料行在索引中當做索引鍵或無索引鍵資料行時,內含無索引鍵資料行索引可以大幅改進查詢效能。因為查詢最佳化工具可以在索引中找到所有資料行值,所以可以提高效能;不存取資料表或叢集索引資料,導致磁碟 I/O 作業變少。

ms190806.note(zh-tw,SQL.90).gif附註:
索引包含查詢參考的所有資料行時,通常就是指涵蓋查詢。

索引鍵資料行儲存在索引的所有分葉層級上,而無索引鍵資料行僅儲存在分葉層級上。如需有關索引層級的詳細資訊,請參閱<資料表與索引組織>。

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

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

例如,假設在AdventureWorks 範例資料庫中,您要建立 Document 資料表中下列資料行的索引:

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

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

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

內含資料行索引指導方針

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

  • 無索引鍵資料行是定義於 CREATE INDEX 陳述式的 INCLUDE 子句。
  • 無索引鍵資料行僅能定義於資料表或索引檢視的非叢集索引上。
  • 除了 textntextimage,允許所有資料類型。
  • 具決定性之精確或非精確的計算資料行都可以當做內含資料行。如需詳細資訊,請參閱<在計算資料行上建立索引>。
  • 如同索引鍵資料行,只要計算資料行資料類型可以作為無索引鍵索引資料行,則從 imagentexttext 衍生的計算資料行即可以是無索引鍵 (內含) 資料行。
  • 資料行名稱無法同時指定於 INCLUDE 清單與索引鍵資料行清單兩者中。
  • 資料行名稱在 INCLUDE 清單中不得重複。

資料行大小指導方針

  • 至少必須定義一個索引鍵資料行。無索引鍵資料行數目的上限為 1023 個資料行。這是資料表資料行數目的上限減 1。
  • 索引鍵資料行 (不包含無索引鍵資料行) 必須遵守現有索引大小的限制 (上限為 16 個索引鍵資料行),且索引鍵總大小為 900 個位元組。
  • 所有無索引鍵資料行大小總計僅由 INCLUDE 子句中指定的資料行大小限定;例如,varchar(max) 資料行是限定為 2 GB。

資料行修改指導方針

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

  • 必須先卸除索引,才能從資料表卸除無索引鍵資料行。
  • 除非執行下列動作,否則無法變更無索引鍵資料行:
    • 將資料行的 Null 屬性從 NOT NULL 變更為 NULL。
    • 增加 varcharnvarcharvarbinary 資料行的長度。
      ms190806.note(zh-tw,SQL.90).gif附註:
      這些資料行修改限制也適用索引鍵資料行。

設計建議

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

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

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

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

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

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

效能考量因素

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

  • 頁面上可以放入的索引資料列變少。這將使得 I/O 的作業增加而降低快取的效率。
  • 必須有更多磁碟空間才能儲存索引。尤其是,加入 varchar(max)nvarchar(max)varbinary(max)xml 資料類型作為無索引鍵資料行,將大幅增加磁碟空間的需求。這是因為資料行的值複製到索引的分葉層級。因此,它們會同時存在於索引與基底資料表中。
  • 維護索引時,會增加修改、插入、更新或刪除基礎資料表或索引檢視的時間。

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

請參閱

概念

建立索引 (Database Engine)
建立內含資料行的索引
一般索引設計指導方針
索引設計基本概念
索引鍵的大小上限
檢視索引資訊

說明及資訊

取得 SQL Server 2005 協助