建立叢集索引Create Clustered Indexes

適用於: 是SQL Server是Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

您可以使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL,在資料表上建立叢集索引。You can create clustered indexes on tables by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. 除了極少數的例外狀況之外,每個資料表都應該要有叢集索引。With few exceptions, every table should have a clustered index. 除了可以改善查詢效能以外,叢集索引還能夠視需要加以重建或重新組織,以便控制資料表分散程度。Besides improving query performance, a clustered index can be rebuilt or reorganized on demand to control table fragmentation. 檢視上也可以建立叢集索引。A clustered index can also be created on a view. (叢集索引是在 叢集與非叢集索引說明主題中進行定義。)(Clustered indexes are defined in the topic Clustered and Nonclustered Indexes Described.)

本主題內容In This Topic

開始之前Before You Begin

一般實作Typical Implementations

實作叢集索引的方法如下:Clustered indexes are implemented in the following ways:

  • PRIMARY KEY 與 UNIQUE 條件約束PRIMARY KEY and UNIQUE constraints

    建立 PRIMARY KEY 條件約束時,若資料表上沒有存在叢集索引,而且您未指定唯一的非叢集索引,則資料行上會自動建立唯一的叢集索引。When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. 主索引鍵資料行不允許 NULL 值。The primary key column cannot allow NULL values.

    當您建立 UNIQUE 條件約束時,依預設會建立唯一的非叢集索引,以強制 UNIQUE 條件約束。When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. 若資料表上還沒有叢集索引,您可以指定唯一叢集索引。You can specify a unique clustered index if a clustered index on the table does not already exist.

    隨條件約束一起建立的索引,會自動採用與條件約束名稱相同的名稱。An index created as part of the constraint is automatically given the same name as the constraint name. 如需相關資訊,請參閱 Primary and Foreign Key ConstraintsUnique Constraints and Check ConstraintsFor more information, see Primary and Foreign Key Constraints and Unique Constraints and Check Constraints.

  • 獨立於條件限制之外的索引Index independent of a constraint

    如果已指定非叢集的主索引鍵條件約束,您可以在主索引鍵資料行以外的資料行上建立叢集索引。You can create a clustered index on a column other than primary key column if a nonclustered primary key constraint was specified.

限制事項Limitations and Restrictions

  • 建立叢集索引結構時,個別的檔案和檔案群組中都必須有磁碟空間來保存舊 (來源) 結構和新 (目標) 結構。When a clustered index structure is created, disk space for both the old (source) and new (target) structures is required in their respective files and filegroups. 除非認可整個交易,否則舊結構的配置不會取消。The old structure is not deallocated until the complete transaction commits. 此外,可能還需要額外的暫存磁碟空間以供排序之用。Additional temporary disk space for sorting may also be required. 如需詳細資訊,請參閱 Disk Space Requirements for Index DDL OperationsFor more information, see Disk Space Requirements for Index DDL Operations.

  • 若叢集的索引建立於含多個現有非叢集索引的堆積上,所有的非叢集索引都必須重建,以便讓它們包含叢集索引鍵值,而非資料列識別碼 (RID)。If a clustered index is created on a heap with several existing nonclustered indexes, all the nonclustered indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). 同樣地,若擁有多個非叢集索引之資料表上的叢集索引被卸除了,非叢集索引將會全部重建,做為 DROP 作業的一部份。Similarly, if a clustered index is dropped on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. 在大型資料表中,這可能會花許多時間。This may take significant time on large tables.

    在大型資料表上建立索引的較佳方式是以叢集索引開始,然後建立任何非叢集索引。The preferred way to build indexes on large tables is to start with the clustered index and then build any nonclustered indexes. 當您在現有資料表上建立索引時,請考慮將 ONLINE 選項設定為 ON。Consider setting the ONLINE option to ON when you create indexes on existing tables. 設定為 ON 時,將不會長期鎖定資料表。When set to ON, long-term table locks are not held. 這樣一來,基礎資料表上就可以繼續執行查詢或更新動作。This enables queries or updates to the underlying table to continue. 如需詳細資訊,請參閱 Perform Index Operations OnlineFor more information, see Perform Index Operations Online.

  • 叢集索引的索引鍵所包含的 varchar 資料行不能在 ROW_OVERFLOW_DATA 配置單位中有現有的資料。The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. 如果在 varchar 資料行上建立叢集索引,且現有的資料在 IN_ROW_DATA 配置單位中,則後續在可能發送資料非資料列的資料行上進行的插入或更新動作會失敗。If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. 若要取得可能包含資料列溢位資料之資料表的相關資訊,請使用 sys.dm_db_index_physical_stats (Transact-SQL) 動態管理函數。To obtain information about tables that might contain row-overflow data, use the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management function.

SecuritySecurity

權限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 StudioUsing SQL Server Management Studio

若要使用物件總管建立叢集索引To create a clustered index by using Object Explorer

  1. 在 [物件總管] 中,展開要在其中建立叢集索引的資料表。In Object Explorer, expand the table on which you want to create a clustered index.

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

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

  4. 按一下 [索引鍵資料行] 下的 [新增...] 。Under Index key columns, click Add....

  5. 在 [從 tablename 選取資料行]_ 對話方塊中,選取要新增至叢集索引的資料表資料行核取方塊。In the Select Columns fromtable_name dialog box, select the check box of the table column to be added to the clustered index.

  6. 按一下 [確定] 。Click OK.

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

若要使用資料表設計工具建立叢集索引To create a clustered index by using the Table Designer

  1. 在 [物件總管] 中,展開要在其中建立包含叢集索引之資料表的資料庫。In Object Explorer, expand the database on which you want to create a table with a clustered index.

  2. 以滑鼠右鍵按一下 [資料表] 資料夾,然後按一下 [新增資料表…] 。Right-click the Tables folder and click New Table....

  3. 像平常一樣,建立新資料表。Create a new table as you normally would. 如需詳細資訊,請參閱建立資料表 (Database Engine)For more information, see Create Tables (Database Engine).

  4. 以滑鼠右鍵按一下上面建立的新資料表,然後按一下 [設計] 。Right-click the new table created above and click Design.

  5. 在 [資料表設計工具] 功能表上,按一下 [索引/索引鍵] 。On the Table Designer menu, click Indexes/Keys.

  6. 在 [索引/索引鍵] 對話方塊中,按一下 [加入] 。In the Indexes/Keys dialog box, click Add.

  7. 從 [選取的主索引鍵/唯一索引鍵或索引] 文字方塊中選取新索引。Select the new index in the Selected Primary/Unique Key or Index text box.

  8. 在方格中,選取 [建立成 CLUSTERED] ,然後從屬性右邊的下拉式清單中選擇 [是] 。In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property.

  9. 按一下 [關閉] 。Click Close.

  10. 在 [檔案] 功能表上,按一下 [儲存 tablename ]_On the File menu, click Savetable_name.

使用 Transact-SQLUsing Transact-SQL

若要建立叢集索引To create a clustered index

  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  
    -- Create a new table with three columns.  
    CREATE TABLE dbo.TestTable  
        (TestCol1 int NOT NULL,  
         TestCol2 nchar(10) NULL,  
         TestCol3 nvarchar(50) NULL);  
    GO  
    -- Create a clustered index called IX_TestTable_TestCol1  
    -- on the dbo.TestTable table using the TestCol1 column.  
    CREATE CLUSTERED INDEX IX_TestTable_TestCol1   
        ON dbo.TestTable (TestCol1);   
    GO  
    

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

另請參閱See Also

建立主索引鍵 Create Primary Keys
建立唯一的條件約束Create Unique Constraints