索引設計是數據庫設計中比較重要的一個環節，對數據庫的性能其中至關重要的作用，但是索引的設計卻又不是那么容易的事情，性能也不是那么輕易就獲取到的，很多的技術人員因為不恰當的創建索引，最后使得其效果適得其反，可以說“成也索引，敗也索引”。
本系列文章來自Stairway to SQL Server Indexes，翻譯和整理后發布在agilesharp和博客園,希望對廣大的技術朋友在如何使用索引上有所幫助。

唯一索引和約束
唯一索引和其它索引本質上并沒有什么不同，唯一不同的是唯一索引不允許索引鍵中存在相同的值。因為索引中每一個條目都與表中的行對應。唯一索引不允許重復值被插入索引也就保證了對應的行不允許被插入索引所在的表,這也是為什么唯一索引能夠實現主鍵和候選鍵。
為表聲明主鍵或唯一約束時，SQL Server會自動創建與之對應的唯一索引。你可以在沒有唯一約束的情況下創建唯一索引，但反之則不行。定義一個約束時，SQL Server會自動創建一個與之同名的索引，并且你不能在刪除約束之前刪除索引。但可以刪除約束，刪除約束也會導致與之關聯的索引被刪除。
每個表中可以包含多個唯一索引。比如說AdventureWorks的Product表，含有四個唯一索引，分別是ProductID,ProductNumber,rowguid和ProductNameColumn,設置Product表的人將ProductID作為主鍵，其它三個作為候選鍵。
你可以通過Create INDEX語句創建唯一索引，比如:
CREATE UNIQUE NONCLUSTERED INDEX [AK_Product_Name] ON Production.Product (? [Name] );

也可以通過直接定義約束創建唯一索引:
ALTER TABLE Production.Product ADD CONSTRAINT PK_Product_ProductID PRIMARY KEY? CLUSTERED ( ProductID );

上面第一種方法，你Prodcut表中不能含有相同的ProductName,第二種情況表中不允許存在相同的ProductID。
因為定義一個主鍵或是定義約束會導致索引被創建，所以你必須在約束定義時就給出必要的索引信息，因此上面ALTER TABLE語句中包含了”CLUSTERED”關鍵字。
如果唯一索引或約束所約束的列在當前的表中已經含有了重復值，那么創建索引會失敗。
而當唯一索引創建成功后，所有違反這個約束的DML語句都會失敗，比如，我們打算加入一條當前表中存在的的ProductName，語句如下:
INSERT ? ?Production.Product?
( Name ,?
ProductNumber ,?
Color ,?
SafetyStockLevel ,?
ReorderPoint ,?
StandardCost ,?
ListPrice ,?
Size ,?
SizeUnitMeasureCode ,?
WeightUnitMeasureCode ,?
[Weight] ,?
DaysToManufacture ,?
ProductLine ,?
Class ,?
Style ,?
ProductSubcategoryID ,?
ProductModelID ,?
SellStartDate ,?
SellEndDate ,?
DiscontinuedDate?
)? VALUES ? ?( 'Full-Finger Gloves, M' ,?
'A unique product number' ,?
'Black' ,?
4 ,?
3 ,?
20.00 ,?
40.00 ,?
'M' ,?
NULL ,?
NULL ,?
NULL ,?
0 ,?
'M' ,?
NULL ,?
'U' ,?
20 ,?
3 ,?
GETDATE() ,?
GETDATE() ,?
NULL?
) ;?


代碼1.插入的行和表中存在相同的ProductName

上面代碼執行后我們可以看到如下報錯信息:
消息 2601，級別 14，狀態 1，第 1 行? 不能在具有唯一索引 'AK_Product_Name' 的對象? 'Production.Product' 中插入重復鍵的行。? 語句已終止。?

上面的消息告訴我們AK_Product_Name索引不允許我們插入的數據含有和當前表中一樣的ProductName。

主鍵，唯一約束和沒有約束
主鍵約束和唯一約束有如下細小的差別。?
主鍵約束不允許出現NULL值。任何索引的索引鍵都不允許包含null值。但唯一約束允許包含NULL值，但唯一約束把兩個NULL值當作重復值，所以施加了唯一約束的每一列只允許包含一個NULL值。 創建主鍵時會自動創建聚集索引，除非當前表中已經含有了聚集索引或是創建主鍵時指定了NONCLUSTERED關鍵字。 創建唯一約束時會自動創建非聚集索引，除非你指定了CLUSTERED關鍵字并且當前表中還沒有聚集索引。 每個表中只能有一個主鍵，但可以由多個唯一約束。
對于唯一約束和唯一索引的選擇，請參照MSDN上的指導，如下:
唯一約束和唯一索引并沒有顯著的區別。創建獨立的唯一索引和使用唯一約束對于數據的驗證方式并無區別。查詢優化器也不會區分唯一索引是由約束創建還是手工創建。然而以數據完整性為目標的話，最好創建約束，這使得對應的索引的目標一目了然。

混合唯一索引和過濾索引
上面我們提到過唯一索引只允許一個NULL值，但這和常見的業務需求有沖突。很多時候我們對于已經存在的值不允許重復，但是允許存在多個沒有值的列。
比如說吧，你是一個供貨商，你所有的產品都來自于第三方廠商。你將你這里所有的商品信息都存在一個叫做ProductDemo的表中。你有自己的ProductID,還追蹤產品的UPC（Universal Product Code）值。但并不是所有的廠商產品都存在UPC,你表中的部分數據如下所示。
ProductID UPCode Other Columns 主鍵 唯一索引 ? ? 14AJ-W 036000291452
23CZ-M ? 23CZ-L ? 18MM-J 044000865867
表1.ProductDemo表的部分內容

在上表中第二列，你既要保證UPCode的唯一性，又要保證允許NULL值。實現這種需求最好的辦法就是混合唯一索引和過濾索引(過濾索引實在SQL Server 2008中引入的)。
作為演示，我們創建了表1所示的表.
CREATE TABLE ProductDemo?
(?
ProductID NCHAR(6) NOT NULL?
PRIMARY KEY ,?
UPCode NCHAR(12) NULL?
) ;?


接下來我們插入如上所示的數據.
INSERT ?ProductDemo? (? ProductID, UPCode )? VALUES ?( '14AJ-W', '036000291452' )?
, ? ? ? ( '23CZ-M', NULL )?
, ? ? ? ( '23CZ-L', NULL )?
, ? ? ? ( '18MM-J', '044000865867' ) ;?


當我們插入重復值時
INSERT ProductDemo (ProductID , UPCode) VALUES ('14AJ-K', '036000291452');?
收到如下錯誤
消息 2601，級別 14，狀態 1，第 1 行? 不能在具有唯一索引 'xx' 的對象 'dbo.ProductDemo' 中插入重復鍵的行。?
語句已終止。?

(譯者注，這里原文作者應該是疏忽了，略坑爹，因為他沒有創建過濾唯一索引，所以按照原文不會報錯，我在這里加上了,代碼：CREATE UNIQUE NONCLUSTERED INDEX xx on ProductDemo(UPCode) where UPCode!=null)

選擇合適的IGNORE_DUP_KEY選項

當你創建唯一索引時，你可以指定IGNORE_DUP_KEY選項，因此本文最開始創建唯一索引的選項可以是:
CREATE UNIQUE NONCLUSTERED INDEX AK_Product_Name ON Production.Product ( [Name]? ) WITH ( IGNORE_DUP_KEY = OFF );?

IGNORE_DUP_KEY這個名字容易讓人誤會。唯一索引存在時重復的值永遠不會被忽略。更準確的說，唯一索引中永遠不允許存在重復鍵。這個選項的作用僅僅是在多列插入時有用。
比如，你有兩個表，表A和表B，有著完全相同的結構。你可能提交如下語句給SQL Server。
INSERT INTO TableA SELECT * FROM TableB;

SQL Server會嘗試將所有表B中的數據插入表A。但如果因為唯一索引拒絕表B中含有和表A相同的數據插入A怎么辦？你是希望僅僅重復數據插入不成功，還是整個INSERT語句不成功？
這個取決于你設定的IGNORE_DUP_KEY參數，當你創建唯一索引時，通過設置設個參數可以設定當插入不成功時怎么辦，設置IGNORE_DUP_KEY的兩種參數解釋如下:
IGNORE_DUP_KEY=OFF
整個INSERT語句都不會成功并彈出錯誤提示，這也是默認設置。

IGNORE_DUP_KEY=OFF
只有那些具有重復鍵的行不成功，其它所有的行會成功。并彈出警告信息。

IGNORE_DUP_KEY 選項僅僅影響插入語句。而不會被UPDATE,CREATE INDEX,ALTER INDEX所影響。這個選項也可以在設置主鍵和唯一約束時進行設置。

為什么唯一索引可以提供額外的性能提升
唯一索引可以提供出乎你意料之外的性能提升。這是因為唯一索引給SQL Server提供了確保某一列絕對沒有重復值的信息。adventureWork的Product表中的ProductID和ProductName這兩個唯一索引，提供了很好的例子。
加入，你們公司數據倉庫的某個哥們希望你給他提供Product表的一些信息，要求如下:
產品名稱 產品銷售的數量 總銷售額 因此，你寫了如下的查詢語句:
SELECT ?[Name] ,? COUNT(*) AS? 'RowCount' ,? SUM(LineTotal) AS? 'TotalValue'? FROM ? ?Production.Product P?
JOIN Sales.SalesOrderDetail D ON? D.ProductID = P.ProductID? GROUP BY P.Name

(譯者注，這里原作者給的代碼有問題，ProductID替換為P.Name)
數據倉庫的哥們對你的查詢語句很滿意，每一行都包含了產品名稱，銷售數量和總的銷售額，查詢出來的部分結果如下:
index81

但是，你對于這個查詢的成本有所擔心。SalesOrderDetail是上面查詢中兩個表中比較大的表，并且還按照ProductName進行分組，這個ProductName是來自Product表而不是SalesOrderDetail表。
通過SQL Server Management Studio,你注意到SalesOrderDetail表有主鍵，并且主鍵也是聚集索引鍵，也就是SalesOrderID和SalesOrderDetailID,這個主鍵并不會給按照ProductName分組帶來性能提升。
如果你運行了第五篇包含列的代碼，你創建了如下非聚集索引。
CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate? ON? Sales.SalesOrderDetail? (? ProductID,? ModifiedDate? )? INCLUDE?
(? OrderQty,? UnitPrice,? LineTotal? ) ;?


你覺得這個索引可以對你的查詢有幫助因為這個索引包含了除了ProductName列的所有查詢所需的信息。并且這個索引是按照ProductID進行排序的，但你仍然擔心分組的ProductID來自其中一個表而Select的信息來自另一個表。
你通過SQL Server Management Studio,通過查看執行計劃，看到前面數據倉庫那哥們要的查詢的執行計劃如圖1所示。
index82
圖1.按Product.Name進行分組時的執行計劃

首先你可以驚訝于Product表的Product name索引，Product.AK_Product_Name沒有被使用.然后你意識到在Product.Name列上和Product.ProductID上有唯一索引，這使得SQL Server知道這兩列是唯一的。因此，Group By Name等效于Group By ProductID。這使得一個產品一個組。
因此，查詢優化器意識到你的查詢等同于如下查詢，這兩個ProductID索引因此支持對所求查詢的Join和group操作。
SELECT ?[Name] ,? COUNT(*) AS? 'RowCount' ,? SUM(LineTotal) AS? 'TotalValue'? FROM ? ?Production.Product P?
JOIN Sales.SalesOrderDetail D ON? D.ProductID = P.ProductID? GROUP BY ProductID
SQL Server會同時掃描SalesOrderDetail上的覆蓋索引和聚集索引，這兩個索引都是以ProductID進行排序的。因此使用合并連接，而免去了排序或散列操作，總之SQL Server生成了最有效的查詢計劃。
如果你Drop了Product.AK_Product_Name索引，比如:
IF EXISTS ( SELECT *? FROM sys.indexes? WHERE OBJECT_ID =? OBJECT_ID(N'Production.Product')? AND name = N'AK_Product_Name')? DROP? INDEX AK_Product_Name? ON Production.Product;?


那么生成的新的執行計劃就沒有那么有效了，需要額外的排序和合并連接操作。
index83
圖2.當Drop掉索引后，按照Product Name進行分組的查詢的執行計劃

你可以看到，雖然唯一索引的主要功能是保證數據的完整性，還可以幫助查詢優化器生成更好的查詢計劃，即使這個索引本身不被用來訪問數據。

總結
唯一索引為主鍵和候選鍵提供了約束。唯一索引可以在沒有唯一約束時存在，反之則不行。
唯一索引同時也可以是過濾索引，這使得唯一索引可以允許一列中有多個NULL值。
IGNORE_DUP_KEY 關鍵字可以影響批量插入語句。
唯一索引還可以提供更好的性能，既然唯一索引本身并沒有用于數據訪問。
分類: SQL Server 索引

本文轉自CareySon博客園博客，原文鏈接：http://www.cnblogs.com/CareySon/archive/2012/09/08/2676219.html如需轉載請自行聯系原作者

總結

以上是生活随笔為你收集整理的【译】SQL Server索引进阶第八篇：唯一索引的全部內容，希望文章能夠幫你解決所遇到的問題。

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