建立內含資料行的索引Create Indexes with Included Columns

本主題的適用對象: 是SQL Server是Azure SQL Database沒有Azure SQL 資料倉儲沒有Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

此主題描述如何使用 SQL ServerSQL ServerTransact-SQLSQL Server Management Studio ,在 Transact-SQLTransact-SQL中加入內含 (或非索引鍵) 資料行,以擴充非叢集索引的功能。This topic describes how to add included (or nonkey) columns to extend the functionality of nonclustered indexes in SQL ServerSQL Server by using Transact-SQLSQL Server Management Studio or Transact-SQLTransact-SQL. 藉由加入非索引鍵資料行,您可以建立涵蓋更多查詢的非叢集索引。By including nonkey columns, you can create nonclustered indexes that cover more queries. 這是因為非索引鍵之索引資料行有下列好處:This is because the nonkey columns have the following benefits:

  • 與索引鍵資料行一樣,它們可以是不允許的資料類型。They can be data types not allowed as index key columns.

  • 計算索引鍵資料行數或索引鍵大小時, Database EngineDatabase Engine 不會考慮它們。They are not considered by the Database EngineDatabase Engine when calculating the number of index key columns or index key size.

    查詢中所有的資料行在索引中當做索引鍵或非索引鍵資料行時,非索引鍵資料行的索引可以大幅改進查詢效能。An index with nonkey columns can significantly improve query performance when all columns in the query are included in the index either as key or nonkey columns. 因為查詢最佳化工具可以在索引中找到所有資料行值,所以可以提高效能;不存取資料表或叢集索引資料,導致磁碟 I/O 作業變少。Performance gains are achieved because the query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations.

注意

索引包含查詢參考的所有資料行時,通常就是指「涵蓋查詢」。When an index contains all the columns referenced by a query it is typically referred to as covering the query.

本主題內容In This Topic

開始之前 Before You Begin

設計建議 Design Recommendations

  • 重新設計具有大型索引鍵大小的非叢集索引,如此僅有用於搜尋與查閱的資料行才會是索引鍵資料行。Redesign nonclustered indexes with a large index key size so that only columns used for searching and lookups are key columns. 讓涵蓋查詢的所有其他資料行都做為非索引鍵資料行。Make all other columns that cover the query into nonkey columns. 如此一來,您將擁有涵蓋查詢所需的所有資料行,但是索引鍵本身會變得很小而且很有效率。In this way, you will have all columns needed to cover the query, but the index key itself is small and efficient.

  • 在非叢集索引中包含非索引鍵資料行,以避免超出目前索引大小限制:最大 32 個索引鍵資料行,最大 1,700 個位元組索引鍵大小 (在 SQL Server 2016SQL Server 2016 之前為 16 個索引鍵資料行和 900 個位元組)。Include nonkey columns in a nonclustered index to avoid exceeding the current index size limitations of a maximum of 32 key columns and a maximum index key size of 1,700 bytes (16 key columns and 900 bytes prior to SQL Server 2016SQL Server 2016). 計算索引鍵資料行數或索引鍵大小時, Database EngineDatabase Engine 不會考慮非索引鍵之索引資料行。The Database EngineDatabase Engine does not consider nonkey columns when calculating the number of index key columns or index key size.

限制事項 Limitations and Restrictions

  • 非索引鍵資料行只能在非叢集索引上定義。Nonkey columns can only be defined on nonclustered indexes.

  • 除了 textntextimage ,所有資料類型都可以用做非索引鍵資料行。All data types except text, ntext, and image can be used as nonkey columns.

  • 具決定性之精確或非精確的計算資料行都可以當做非索引鍵資料行。Computed columns that are deterministic and either precise or imprecise can be nonkey columns. 如需詳細資訊,請參閱 計算資料行的索引For more information, see Indexes on Computed Columns.

  • 只要計算資料行資料類型允許非索引鍵索引資料行,從 imagentexttext 資料類型衍生的計算資料行就可以是非索引鍵資料行。Computed columns derived from image, ntext, and text data types can be nonkey columns as long as the computed column data type is allowed as a nonkey index column.

  • 必須先卸除資料表的索引,才能從資料表卸除非索引鍵資料行。Nonkey columns cannot be dropped from a table unless that table’s index is dropped first.

  • 除非執行下列動作,否則無法變更非索引鍵之索引資料行:Nonkey columns cannot be changed, except to do the following:

    • 將資料行的 Null 屬性從 NOT NULL 變更為 NULL。Change the nullability of the column from NOT NULL to NULL.

    • 增加 varcharnvarcharvarbinary 資料行的長度。Increase the length of varchar, nvarchar, or varbinary columns.

安全性 Security

Permissions Permissions

需要資料表或檢視表的 ALTER 權限。Requires ALTER permission on the table or view. 使用者必須是 系統管理員 固定伺服器角色的成員,或是 db_ddladmindb_owner 固定資料庫角色的成員。User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

使用 SQL Server Management Studio Using SQL Server Management Studio

建立非索引鍵資料行的索引To create an index with nonkey columns

  1. 在 [物件總管] 中,按一下加號展開資料庫,此資料庫包含您要建立非索引鍵資料行之索引的資料表。In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create an index with nonkey columns.

  2. 按一下加號展開 [資料表] 資料夾。Click the plus sign to expand the Tables folder.

  3. 按一下加號展開要建立非索引鍵資料行之索引的資料表。Click the plus sign to expand the table on which you want to create an index with nonkey columns.

  4. 以滑鼠右鍵按一下 [索引] 資料夾,指向 [新增索引],然後選取 [非叢集索引]。Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….

  5. [新增索引] 對話方塊,於 [一般] 頁面上的 [索引名稱] 方塊中輸入新索引的名稱。In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. 按一下 [索引鍵資料行] 索引標籤底下的 [加入]Under the Index key columns tab, click Add….

  7. 在 [從 table_name 選取資料行] 對話方塊中,選取要加入索引之一或多個資料表資料行的核取方塊。In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the index.

  8. 按一下 [確定]Click OK.

  9. 按一下 [包含的資料行] 索引標籤底下的 [加入]Under the Included columns tab, click Add….

  10. 在 [從 table_name 選取資料行] 對話方塊中,選取要加入至索引中作為非索引鍵資料行之資料表資料行的核取方塊。In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the index as nonkey columns.

  11. 按一下 [確定]Click OK.

  12. [新增索引] 對話方塊中,按一下 [確定]In the New Index dialog box, click OK.

使用 Transact-SQL Using Transact-SQL

建立非索引鍵資料行的索引To create an index with nonkey columns

  1. [物件總管]中,連接到 Database EngineDatabase Engine的執行個體。In Object Explorer, connect to an instance of Database EngineDatabase Engine.

  2. 在標準列上,按一下 [新增查詢]On the Standard bar, click New Query.

  3. 將下列範例複製並貼入查詢視窗中,然後按一下 [執行]Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;  
    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  
    

    如需詳細資訊,請參閱 CREATE INDEX (Transact-SQL)For more information, see CREATE INDEX (Transact-SQL).