創建唯一聚集索引典型實現

唯一索引可通過以下方式實現：

PRIMARY KEY 或 UNIQUE 約束

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

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

有關詳細信息，請參閱 Unique Constraints and Check Constraints 和 Primary and Foreign Key Constraints。

獨立于約束的索引

可以為一個表定義多個唯一非聚集索引。

有關詳細信息，請參閱 CREATE INDEX (Transact-SQL)。

索引視圖

若要創建索引視圖，請對一個或多個視圖列定義唯一聚集索引。 視圖將執行，并且結果集存儲在該索引的頁級別中，其存儲方式與表數據存儲在聚集索引中的方式相同。 有關詳細信息，請參閱 創建索引視圖。

創建唯一聚集索引限制和局限

如果數據中存在重復的鍵值，則不能創建唯一索引、UNIQUE 約束或 PRIMARY KEY 約束。

唯一非聚集索引可以包括包含性非鍵列。 有關詳細信息，請參閱 Create Indexes with Included Columns。

使用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> | } [ ,...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)。

-- 可以指定為以單詞 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'表名',N'index',N'索引名稱';

go

示例：

--聲明數據庫應用

use testss;

go

--判斷是否存在索引

if exists(select * from sysindexes where name='uniqueclus1')

drop index uniqueclus1 on test1 with (online=off);

go

create

unique --唯一

clustered --聚集索引

index --索引關鍵字

uniqueclus1 --索引名稱

on test1 --索引建立在哪張表

(name asc) --索引建立在哪個數據列

with(

--pad_index:指定索引填充

--pad_index=on:FILLFACTOR 指定的可用空間百分比應用于索引的中間級頁。

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

pad_index=on,

--statistics_norecompute:指定是否重新計算統計信息。

--statistics_norecompute=on:過時的統計信息不會自動重新計算。

--statistics_norecompute=off:啟用自動統計信息更新。

statistics_norecompute=on,

--sort_in_tempdb:指定是否將排序結果存儲在 tempdb 中。

--sort_in_tempdb=on:在tempdb中存儲用于生成索引的中間排序結果。如果tempdb與用戶數據庫不在同一組磁盤上，就可縮短創建索引所需的時間。但是，這會增加索引生成期間所使用的磁盤空間量。

--sort_in_tempdb=off:中間排序結果與索引存儲在同一數據庫中。

sort_in_tempdb=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=off,

--drop_existing：表示如果這個索引還在表上就 drop 掉然后在 create 一個新的。 默認為 OFF。

--drop_existing=on:指定要刪除并重新生成現有索引，其必須具有相同名稱作為參數 index_name。

--drop_existing=off:指定不刪除和重新生成現有的索引。 如果指定的索引名稱已經存在，SQL Server 將顯示一個錯誤。

drop_existing=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=off,

--allow_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,

--maxdop=max_degree_of_parallelism:在索引操作期間替代 max degree of parallelism 配置選項。 有關詳細信息，請參閱 配置 max degree of parallelism 服務器配置選項。 使用 MAXDOP 可以限制在執行并行計劃的過程中使用的處理器數量。 最大數量為 64 個處理器。

--max_degree_of_parallelism 可以是：

--1 - 取消生成并行計劃。

-->1 - 將并行索引操作中使用的最大處理器數量限制為指定數量。

--0（默認值）- 根據當前系統工作負荷使用實際數量的處理器或更少數量的處理器。

--有關詳細信息，請參閱 配置并行索引操作。

maxdop=1

--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

--on partitions ( { <partition_number_expression> | } [ ,...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)。

-- 可以指定為以單詞 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'uniqueclus1';

go

創建唯一索引優缺點

優點：

1、多列唯一索引能夠保證索引鍵中值的每個組合都是唯一的。

2、只要每個列中的數據是唯一的，就可以為同一個表創建一個唯一聚集索引。

3、唯一索引能夠確保定義的列的數據完整性。

4、唯一索引提供幫助查詢優化器生成更高效的執行計劃的其他信息。

5、創建唯一索引只會創建一個唯一索引，不會創建約束。

缺點：

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

2、索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

3、當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

總結

以上是生活随笔為你收集整理的SQLServer之创建唯一聚集索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。