聚集索引添加規則聚集索引按下列方式實現 PRIMARY KEY 和 UNIQUE 約束在創建 PRIMARY KEY 約束時，如果不存在該表的聚集索引且未指定唯一非聚集索引，則將自動對一列或多列創建唯一聚集索引。主鍵列不允許空值。在創建 UNIQUE 約束時，預設情況下將創建唯一非聚集索引， ...

聚集索引添加規則

聚集索引按下列方式實現

PRIMARY KEY 和 UNIQUE 約束

在創建 PRIMARY KEY 約束時，如果不存在該表的聚集索引且未指定唯一非聚集索引，則將自動對一列或多列創建唯一聚集索引。主鍵列不允許空值。

在創建 UNIQUE 約束時，預設情況下將創建唯一非聚集索引，以便強制 UNIQUE 約束。如果不存在該表的聚集索引，則可以指定唯一聚集索引。

將索引創建為約束的一部分後，會自動將索引命名為與約束名稱相同的名稱。有關詳細信息，請參閱 Primary and Foreign Key Constraints 和 Unique Constraints and Check Constraints。
獨立於約束的索引

指定非聚集主鍵約束後，您可以對非主鍵列的列創建聚集索引。

限制和局限

創建聚集索引結構後，舊（源）結構和新（目標）結構的各自的文件和文件組都需要磁碟空間。在完成事務提交後，才會釋放舊結構。排序也需要其他臨時磁碟空間。有關詳細信息，請參閱 Disk Space Requirements for Index DDL Operations。
如果對具有多個現有非聚集索引的堆創建聚集索引，則必須重新生成所有非聚集索引，以使它們包含聚集鍵值而非行標識符 (RID)。同樣，如果刪除具有多個非聚集索引的表的聚集索引，在 DROP 操作過程中，將重新生成非聚集索引。對於大型表，這可能需要很長時間。

對大型表創建索引的首選方法是先創建聚集索引，然後創建任何非聚集索引。在對現有表創建索引時，請考慮將 ONLINE 選項設置為 ON。如果設置為 ON，則不會持有長期表鎖。這使對基礎表的查詢或更新可以繼續進行。有關詳細信息，請參閱 Perform Index Operations Online。
聚集索引的索引鍵不能包含在 ROW_OVERFLOW_DATA 分配單元中具有現有數據的 varchar 列。如果對 varchar 列創建了聚集索引，並且在 IN_ROW_DATA 分配單元中存在現有數據，則對該列執行的將數據推送到行外的後續插入或更新操作將會失敗。若要獲得有關可能包含行溢出數據的表的信息，請使用 sys.dm_db_index_physical_stats (Transact-SQL) 動態管理函數。

使用SSMS資料庫管理工具添加聚集索引

使用表設計器創建聚集索引

1、連接資料庫，選擇資料庫，選擇數據表-》右鍵點擊-》選擇設計。

2、在表設計視窗-》選擇要添加索引的數據列-》右鍵點擊-》選擇索引/鍵。

3、在索引/鍵彈出框中-》點擊添加-》在常規視窗中類型選擇索引-》點擊選擇列。

4、在索引列彈出框中-》選擇索引列-》選擇排序方式-》可以把索引建在一列或者多列上-》點擊確定。

5、在索引/鍵彈出框中-》輸入索引名稱-》輸入索引描述-》其它設置可以選擇系統預設也可以選擇自己設置-》點擊關閉。

6、點擊保存(或者ctrl+s)->關閉表設計器-》刷新表-》查看結果。

使用對象資源管理器創建聚集索引

1、連接資料庫，選擇資料庫-》展開數據表-》右鍵單擊索引文件夾-》選擇新建索引-》選擇聚集索引。

2、在新建索引彈出框-》在索性名稱中輸入索引名稱-》選擇是否創建唯一聚集索引-》點擊添加，添加要添加索引的數據列。

3、在數據表彈出框中-》選擇數據列，可以選擇多個-》點擊確定。

4、在新建索引彈出框中-》點擊選項，關於選項配置可以選擇系統預設，也可以根據需要自己配置。

5、在新建索引彈出框中-》點擊存儲，可以選擇預設文件組和分區方案，也可以自行配置。

6、在新建索引彈出框中-》點擊擴展屬性，點擊文件編輯符，輸入索引描述-》單擊確定。

7、在對象資源管理器中即可看到新創建的索引(如果沒有出現可以刷新再次查看)。

使用T-SQL腳本添加聚集索引

語法：

--聲明資料庫引用
use 資料庫名;
go

--判斷索引是否存在
if exists(select * from sysindexes where name=索引名)
drop index 索引名 on 表名 with (online=off);
go

--添加索引
create
--[unique] --指定聚集索引是否唯一
[clustered | nonclustered] --指定為聚集索引
index 索引名稱 --索引名稱
on 表名 --索引添加在哪個表
(列名 [asc | desc],列名 [asc | desc]) --索引添加在哪個數據列
with(

--pad_index:指定索引填充
--pad_index=on:FILLFACTOR 指定的可用空間百分比應用於索引的中間級頁。
--pad_index=off或未指定 fillfactor:考慮到中間級頁上的鍵集，可以將中間級頁幾乎填滿，但至少要為最大索引行留出足夠空間。
pad_index={ on | off },

--statistics_norecompute:指定是否重新計算統計信息。
--statistics_norecompute=on:過時的統計信息不會自動重新計算。
--statistics_norecompute=off:啟用自動統計信息更新。
statistics_norecompute={ on | off },

--sort_in_tempdb:指定是否將排序結果存儲在 tempdb 中。
--sort_in_tempdb=on:在tempdb中存儲用於生成索引的中間排序結果。如果tempdb與用戶資料庫不在同一組磁碟上，就可縮短創建索引所需的時間。但是，這會增加索引生成期間所使用的磁碟空間量。
--sort_in_tempdb=off:中間排序結果與索引存儲在同一資料庫中。
sort_in_tempdb={ on | off },

--ignore_dup_key:指定在插入操作嘗試向唯一索引插入重覆鍵值時的響應類型。 IGNORE_DUP_KEY 選項僅適用於創建或重新生成索引後發生的插入操作。當執行 CREATE INDEX、ALTER INDEX 或 UPDATE 時，該選項無效。預設為 OFF。
--ignore_dup_key=on:打開，將重覆鍵值插入唯一索引時會出現警告消息。只有違反唯一性的行為才會失敗。
--ignore_dup_key=off:關閉,將重覆鍵值插入唯一索引時會出現錯誤消息。回滾整個INSERT操作。對於對視圖創建的索引、非唯一索引、XML 索引、空間索引以及篩選的索引，IGNORE_DUP_KEY 不能設置為 ON
ignore_dup_key={ on | off },

--drop_existing：表示如果這個索引還在表上就 drop 掉然後在 create 一個新的。預設為 OFF。
--drop_existing=on:指定要刪除並重新生成現有索引，其必須具有相同名稱作為參數 index_name。
--drop_existing=off:指定不刪除和重新生成現有的索引。如果指定的索引名稱已經存在，SQL Server 將顯示一個錯誤。
drop_existing={ on | off },

--online:指定在索引操作期間基礎表和關聯的索引是否可用於查詢和數據修改操作。預設為 OFF。 REBUILD 可作為 ONLINE 操作執行。
--online=on:在索引操作期間不持有長期表鎖。在索引操作的主要階段，源表上只使用意向共用 (IS) 鎖。
--這使得能夠繼續對基礎表和索引進行查詢或更新。
--操作開始時，在很短的時間內對源對象持有共用 (S) 鎖。
--操作結束時，如果創建非聚集索引，將在短期內獲取對源的 S（共用）鎖；
--當聯機創建或刪除聚集索引時，以及重新生成聚集或非聚集索引時，將在短期內獲取 SCH-M（架構修改）鎖。但聯機索引鎖是短的元數據鎖，特別是 Sch-M 鎖必須等待此表上的所有阻塞事務完成。
--在等待期間，Sch-M 鎖在訪問同一表時阻止在此鎖後等待的所有其他事務。對本地臨時表創建索引時，ONLINE 不能設置為 ON。
--online=off:在索引操作期間應用表鎖。這樣可以防止所有用戶在操作期間訪問基礎表。
--創建、重新生成或刪除聚集索引或者重新生成或刪除非聚集索引的離線索引操作將對錶獲取架構修改 (Sch-M) 鎖。
--這樣可以防止所有用戶在操作期間訪問基礎表。創建非聚集索引的離線索引操作將對錶獲取共用 (S) 鎖。這樣可以防止更新基礎表，但允許讀操作（如 SELECT 語句）。
online={ on | off },

--aloow_row_locks:指定是否允許行鎖。
--allow_row_locks=on:訪問索引時允許行鎖。資料庫引擎確定何時使用行鎖。
--allow_row_locks=off:不使用行鎖。
allow_row_locks={ on | off },

--allow_page_locks:指定是否允許使用頁鎖。
--allow_page_locks=on:訪問索引時允許頁鎖。資料庫引擎確定何時使用頁鎖。
-- allow_page_locks=off:不使用頁鎖。
allow_page_locks={ on | off },

--fillfactor=n：指定一個百分比，指示在資料庫引擎創建或修改索引的過程中，應將每個索引頁面的葉級填充到什麼程度。指定的值必須是 1 到 100 之間的整數。預設值為 0。
fillfactor=n

--maxdop=max_degree_of_parallelism:在索引操作期間替代 max degree of parallelism 配置選項。有關詳細信息，請參閱配置 max degree of parallelism 伺服器配置選項。使用 MAXDOP 可以限制在執行並行計劃的過程中使用的處理器數量。最大數量為 64 個處理器。
--max_degree_of_parallelism 可以是：
--1 - 取消生成並行計劃。
-->1 - 將並行索引操作中使用的最大處理器數量限製為指定數量。
--0（預設值）- 根據當前系統工作負荷使用實際數量的處理器或更少數量的處理器。
--有關詳細信息，請參閱配置並行索引操作。
--maxdop=max_degree_of_parallelism,

--data_compression=row:為指定的表、分區號或分區範圍指定數據壓縮選項。選項如下所示：
--none
--不壓縮表或指定的分區。僅適用於行存儲表；不適用於列存儲表。
--row
--使用行壓縮來壓縮表或指定的分區。僅適用於行存儲表；不適用於列存儲表。
--page
--使用頁壓縮來壓縮表或指定的分區。僅適用於行存儲表；不適用於列存儲表。
--columnstore
--適用範圍： SQL Server 2014 (12.x) 到 SQL Server 2017。
--僅適用於列存儲表。 COLUMNSTORE 指定對使用 COLUMNSTORE_ARCHIVE 選項壓縮的分區進行解壓縮。還原數據時，將繼續通過用於所有列存儲表的列存儲壓縮對 COLUMNSTORE 索引進行壓縮。
--columnstore_archive
--適用範圍： SQL Server 2014 (12.x) 到 SQL Server 2017。
--僅適用於列存儲表，這是使用聚集列存儲索引存儲的表。 COLUMNSTORE_ARCHIVE 會進一步將指定分區壓縮到更小。這可用於存檔，或者用於要求更少存儲並且可以付出更多時間來進行存儲和檢索的其他情形
--data_compression={ none | row | page | columnstore | columnstore_archive }

--on partitions ( { <partition_number_expression> | <range> } [ ,...n ] ) 適用範圍： SQL Server 2008 到 SQL Server 2017。
--指定對其應用 DATA_COMPRESSION 設置的分區。如果表未分區，ON PARTITIONS 參數將生成錯誤。如果不提供 ON PARTITIONS 子句，DATA_COMPRESSION 選項將應用於已分區表的所有分區。
--可以按以下方式指定 <partition_number_expression>：
--提供一個分區號，例如：ON PARTITIONS (2)。
--提供若幹單獨分區的分區號並用逗號將它們隔開，例如：ON PARTITIONS (1, 5)。
--同時提供範圍和單個分區，例如：ON PARTITIONS (2, 4, 6 TO 8)。
--<range> 可以指定為以單詞 TO 隔開的分區號，例如：ON PARTITIONS (6 TO 8)。
--，請多次指定 DATA_COMPRESSION 選項
--on partitions(1-2)

)
on [primary];--數據空間規範
go

--添加註釋
execute sp_addextendedproperty N'MS_Description',N'索引說明',N'schema',N'dbo',N'table',N'test1',N'index',N'clus1';
go

示例：

--聲明資料庫引用
use testss;
go

--判斷索引是否存在
if exists(select * from sysindexes where name='clus1')
drop index clus1 on test1 with (online=off);
go

--添加索引
create
--[unique] --指定聚集索引是否唯一
clustered --指定為聚集索引
index clus1 --索引名稱
on test1 --索引添加在哪個表
(name asc,sex desc) --索引添加在哪個數據列
with(

--pad_index:指定索引填充
--pad_index=on:FILLFACTOR 指定的可用空間百分比應用於索引的中間級頁。
--pad_index=off或未指定 fillfactor:考慮到中間級頁上的鍵集，可以將中間級頁幾乎填滿，但至少要為最大索引行留出足夠空間。
pad_index=off,

--statistics_norecompute:指定是否重新計算統計信息。
--statistics_norecompute=on:過時的統計信息不會自動重新計算。
--statistics_norecompute=off:啟用自動統計信息更新。
statistics_norecompute=off,

--aloow_row_locks:指定是否允許行鎖。
--allow_row_locks=on:訪問索引時允許行鎖。資料庫引擎確定何時使用行鎖。
--allow_row_locks=off:不使用行鎖。
allow_row_locks=on,

--allow_page_locks:指定是否允許使用頁鎖。
--allow_page_locks=on:訪問索引時允許頁鎖。資料庫引擎確定何時使用頁鎖。
-- allow_page_locks=off:不使用頁鎖。
allow_page_locks=on,

--fillfactor=n：指定一個百分比，指示在資料庫引擎創建或修改索引的過程中，應將每個索引頁面的葉級填充到什麼程度。指定的值必須是 1 到 100 之間的整數。預設值為 0。
fillfactor=1

)
on [primary];--數據空間規範
go

--添加註釋
execute sp_addextendedproperty N'MS_Description',N'第一個聚集索引',N'schema',N'dbo',N'table',N'test1',N'index',N'clus1';
go

聚集索引優缺點

優點：

1、表記錄的排列順序與索引的排列順序一致，查詢速度快。

2、對於那些經常要搜索範圍值的列添加聚集索引執行效率更高。

3、對從表中檢索的數據進行排序時經常要用到某一列，可在該表的該列上創建聚集（物理排序）索引，避免每次查詢該列時都進行排序，節省成本。

缺點：

1、對錶進行修改速度較慢，這是為了保持表中的記錄的物理順序與索引的順序一致，而把記錄插入到數據頁的相應位置，必須在數據頁中進行數據重排，降低了執行速度。

2、索引需要占物理空間。

3、創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加。

SQLServer之添加聚集索引