T-SQL 索引（唯一，聚集，非聚集） 本文譯自MSDN和自己的一些筆記心得

備註：本文譯自MSDN和自己的一些筆記心得，因本人身在台灣！使用的是繁體字！嫌看著蛋疼的請繞道！勿噴！

 

何為索引：就是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。 當然，即使没有索引，SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能

先說說怎麼建立索引吧！

Transact-SQL 語法慣例

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::=
        column_name IN (constant ,…)

<comparison> ::=
        column_name <comparison_op> constant

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::= 
<partition_number_expression> TO <partition_number_expression>


Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
        table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

看著蛋疼加複製對吧!但你只要記住以下幾個就行：

UNIQUE

在資料表或檢視上建立唯一索引。在唯一索引中，任兩個資料列都不能有相同的索引鍵值。檢視中的叢集索引必須是唯一的。

不論 IGNORE_DUP_KEY 是否設為 ON，Database Engine 都不允許在已包含重複值的資料行上建立唯一索引。如果嘗試執行這項作業，Database Engine 會顯示錯誤訊息。必須先移除重複值，才能在資料行上建立唯一索引。唯一索引中使用的資料行應設為 NOT NULL，因為，當唯一索引建立時，多個 Null 值會被視為重複值。

CLUSTERED

建立一個索引，在該索引中，鍵值的邏輯順序決定資料表中之對應資料列的實體順序。叢集索引的層級 (底部或分葉) 包含資料表的實際資料列。資料表或檢視一次只能使用一個叢集索引。如需詳細資訊，請參閱＜叢集索引結構＞。

含有唯一叢集索引的檢視稱為索引檢視。在檢視上建立唯一叢集索引，可將檢視實際具體化。必須先在檢視上建立唯一叢集索引，才能在相同檢視上定義任何其他索引。如需詳細資訊，請參閱＜設計索引檢視＞。

先建立叢集索引，再建立任何非叢集索引。當叢集索引建立時，會重建資料表上現有的非叢集索引。

如果未指定 CLUSTERED，就會建立非叢集索引。

NONCLUSTERED

建立索引來指定資料表的邏輯排序。如果是非叢集索引，資料列的實體順序和它們的索引順序無關。如需詳細資訊，請參閱＜非叢集索引結構＞。

不論建立多少索引，每一份資料表最多只能有 999 個非叢集索引：以隱含的方式使用 PRIMARY KEY 和 UNIQUE 條件約束或以明確的方式使用 CREATE INDEX。

如果是索引檢視，只能在已定義唯一叢集索引的檢視上建立非叢集索引。

預設值是 NONCLUSTERED。

以下是索引的範例：

使用的是AdventureWorks示例數據庫

建立簡單的非叢集索引

下列範例會在 Purchasing.ProductVendor 資料表的 VendorID 資料行上建立非叢集索引。

USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_ProductVendor_VendorID') DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor; GO CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (VendorID); GO

B. 建立簡單的非叢集複合索引

下列範例會在 Sales.SalesPerson 資料表的 SalesQuota 和 SalesYTD資料行上建立非叢集複合索引。

	複製程式碼
USE AdventureWorks GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD') DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ; GO CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson (SalesQuota, SalesYTD); GO

C. 建立唯一的非叢集索引

下列範例會在 Production.UnitMeasure 資料表的 Name 資料行上建立唯一非叢集索引。索引會強制將資料上的唯一性插入Name 資料行中。

USE AdventureWorks; GO IF EXISTS (SELECT name from sys.indexes WHERE name = N'AK_UnitMeasure_Name') DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure; GO CREATE UNIQUE INDEX AK_UnitMeasure_Name ON Production.UnitMeasure(Name); GO

下列查詢會嘗試插入一個含有其值與現有資料列相同的資料列，來測試唯一性條件約束。

--Verify the existing value. SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces'; GO INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate) VALUES ('OC', 'Ounces', GetDate());

產生的錯誤訊息如下：

Server: Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

利用 DROP_EXISTING 來卸除和重新建立索引

下列範例會利用 DROP_EXISTING 選項，在 Production.WorkOrder 資料表的 ProductID 資料行上卸除及重新建立現有的索引。也會設定 FILLFACTOR 和 PAD_INDEX 選項。

USE AdventureWorks; GO CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID ON Production.WorkOrder(ProductID) WITH (FILLFACTOR = 80, PAD_INDEX = ON, DROP_EXISTING = ON); GO

F. 在檢視上建立索引

下列範例在該檢視上建立檢視和索引。內含使用索引檢視的兩項查詢。

USE AdventureWorks; GO --Set the options to support indexed views. SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO --Create view with schemabinding. IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL DROP VIEW Sales.vOrders ; GO CREATE VIEW Sales.vOrders WITH SCHEMABINDING AS SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue, OrderDate, ProductID, COUNT_BIG(*) AS COUNT FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o WHERE od.SalesOrderID = o.SalesOrderID GROUP BY OrderDate, ProductID; GO --Create an index on the view. CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON Sales.vOrders (OrderDate, ProductID); GO --This query can use the indexed view even though the view is --not specified in the FROM clause. SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, OrderDate, ProductID FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID AND ProductID BETWEEN 700 and 800 AND OrderDate >= CONVERT(datetime,'05/01/2002',101) GROUP BY OrderDate, ProductID ORDER BY Rev DESC; GO --This query can use the above indexed view. SELECT OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev FROM Sales.SalesOrderDetail AS od JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID AND DATEPART(mm,OrderDate)= 3 AND DATEPART(yy,OrderDate) = 2002 GROUP BY OrderDate ORDER BY OrderDate ASC; GO

G. 利用內含 (非索引鍵) 資料行建立索引

下列範例會利用一個索引鍵資料行 (PostalCode) 和四個非索引鍵資料行 (AddressLine1、AddressLine2、City、StateProvinceID) 來建立非叢集索引。其後有一個由索引處理的查詢。若要顯示查詢最佳化工具所選取的索引，請先在 SQL Server Management Studio 的 [查詢] 功能表上選取 [顯示實際執行計畫]，再執行查詢。

USE AdventureWorks; GO IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_Address_PostalCode') DROP INDEX IX_Address_PostalCode ON Person.Address; GO CREATE NONCLUSTERED INDEX IX_Address_PostalCode ON Person.Address (PostalCode) INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); GO SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode FROM Person.Address WHERE PostalCode BETWEEN N'98000' and N'99999';