共用方式為

建立內含資料行的索引

  • 發行項

適用於：Microsoft Fabric 中的 SQL ServerAzure SQL 資料庫 Azure SQL 受控執行個體 SQL 資料庫

本文說明如何使用 SQL Server Management Studio 或 Transact-SQL，在 SQL Server 中加入內含 (或非索引鍵) 資料行，以擴充非叢集索引的功能。 藉由加入非索引鍵資料行，您可以建立涵蓋更多查詢的非叢集索引。 這是因為非索引鍵之索引資料行有下列好處：

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

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

注意

索引包含查詢參考的所有資料行時，通常就是指「涵蓋查詢」。

設計建議

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

  • 在非叢集索引中包含非索引鍵資料行，以避免超出目前索引大小限制：最大 32 個索引鍵資料行，最大 1,700 個位元組索引鍵大小 (在 SQL Server 2016 (13.x) 之前為 16 個索引鍵資料行和 900 個位元組)。 計算索引鍵資料行數或索引鍵大小時，資料庫引擎不會考慮非索引鍵資料行。

  • 非索引鍵資料行在索引定義的順序不會影響使用索引的查詢效能。

  • 避免非常寬的非叢集索引，其中包含的資料行不代表是夠窄的基礎資料表資料行子集。 如果新增寬索引，請一律確認更新一個額外寬索引的成本是否會抵銷直接從資料表讀取的成本。

限制事項

  • 非索引鍵資料行只能在非叢集索引上定義。

  • 除了 text、 ntext和 image ，所有資料類型都可以用做非索引鍵資料行。

  • 具決定性之精確或非精確的計算資料行都可以當做非索引鍵資料行。 如需詳細資訊，請參閱 計算資料行的索引。

  • 只要計算資料行資料類型允許非索引鍵索引資料行，從 image、 ntext和 text 資料類型衍生的計算資料行就可以是非索引鍵資料行。

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

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

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

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

安全性

權限

需要資料表或檢視表的 ALTER 權限。 使用者必須是 系統管理員 固定伺服器角色的成員，或是 db_ddladmin 和 db_owner 固定資料庫角色的成員。

使用 SQL Server Management Studio 建立具有非索引鍵資料行的索引

  1. 在 [物件總管] 中，選取加號展開資料庫，此資料庫包含您要建立非索引鍵資料行之索引的資料表。

  2. 選取加號展開 [資料表] 資料夾。

  3. 選取加號展開要建立非索引鍵資料行之索引的資料表。

  4. 以滑鼠右鍵按一下 [索引] 資料夾，指向 [新增索引]，然後選取 [非叢集索引…]。

  5. 在 [新增索引] 對話方塊，於 [一般] 頁面上的 [索引名稱] 方塊中輸入新索引的名稱。

  6. 在 [索引鍵資料行] 索引標籤下方選取 [新增...]。

  7. 在 [從table_name選取數據行] 對話框中，選取要新增至索引之數據表數據行的複選框或複選框。

  8. 選取 [確定]。

  9. 在 [包含的資料行] 索引標籤下方選取 [新增...]。

  10. 在 [從 table_name選取資料行] 對話方塊，選取要加入索引作為非索引鍵資料行的一或多個資料表資料行核取方塊。

  11. 選取 [確定]。

  12. 在 [新增索引] 對話方塊中，選取 [確定]。

使用 Transact-SQL 建立非索引鍵資料行的索引

  1. 在物件總管中，連線到資料庫引擎的執行個體。

  2. 在標準列上，選取 [新增查詢]。

  3. 複製下列範例並將其貼到查詢視窗中，然後選取 [執行]。

    USE AdventureWorks2022;
GO
-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.
-- index key column is PostalCode and the nonkey columns are
-- AddressLine1, AddressLine2, City, and StateProvinceID.
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

相關內容