CREATE INDEX (Transact-SQL)

对表（堆）创建聚集索引或删除和重新创建现有聚集索引时，要求数据库具有额外的可用工作区来容纳数据排序结果和原始表或现有聚集索引数据的临时副本。有关详细信息，请参阅确定索引的磁盘空间要求。有关聚集索引的详细信息，请参阅创建聚集索引。

如果存在唯一索引，数据库引擎会在每次插入操作添加数据时检查重复值。可生成重复键值的插入操作将被回滚，同时数据库引擎显示错误消息。即使插入操作更改多行但只导致出现一个重复值时，也是如此。如果在存在唯一索引并且 IGNORE_DUP_KEY 子句设置为 ON 的情况下输入数据，则只有违反 UNIQUE 索引的行才会失败。有关唯一索引的详细信息，请参阅创建唯一索引。

创建和维护分区索引的方式与已分区表相同，但与普通索引一样，将分区索引作为单独数据库对象来进行处理。可以在未分区的表中使用分区索引，也可以在已分区表中使用未分区索引。

如果要对已分区表创建索引，并且不指定用于放置该索引的文件组，则会按照与基础表相同的方式为该索引分区。这是因为在默认情况下，索引与其基础表放在同一文件组中，并且对应使用相同分区依据列的相同分区方案中的已分区表。

在对非唯一的聚集索引分区时，如果尚未指定分区依据列，则默认情况下数据库引擎将在聚集索引键列表中添加任意分区依据列。

可以使用与为表创建索引时相同的方式，为已分区表创建索引视图。有关分区索引的详细信息，请参阅已分区表和已分区索引。

为视图创建唯一聚集索引可以提高查询性能，因为视图在数据库中的存储方式与具有聚集索引的表的存储方式相同。查询优化器可使用索引视图加快执行查询的速度。要使优化器考虑将该视图作为替换，并不需要在查询中引用该视图。

创建索引视图所需的下列步骤对于成功实现视图而言非常重要：

1. 验证是否视图中将引用的所有现有表的 SET 选项都正确。

2. 在创建任何新表和视图之前，验证会话的 SET 选项设置是否正确。

3. 验证视图定义是否为确定性的。

4. 使用 WITH SCHEMABINDING 选项创建视图。

5. 为视图创建唯一的聚集索引。

索引视图所需的 SET 选项

如果执行查询时启用不同的 SET 选项，则在数据库引擎中对同一表达式求值会产生不同结果。例如，将 SET 选项 CONCAT_NULL_YIELDS_NULL 设置为 ON 后，表达式 'abc' + NULL 会返回值 NULL。但将 CONCAT_NULL_YIEDS_NULL 设置为 OFF 后，同一表达式会生成 'abc'。

为了确保能够正确维护视图并返回一致结果，索引视图需要多个 SET 选项具有固定值。如果下列条件成立，则下表中的 SET 选项必须设置为“必需的值”列中显示的值：

• 索引视图已创建。

• 对构成该索引视图的任何表执行了任何插入、更新或删除操作。包括大容量复制、复制和分发查询等操作。

• 查询优化器使用该索引视图生成查询计划。

  SET 选项	必需的值	默认服务器值	默认 OLE DB 和 ODBC 值	默认 DB-Library 值
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS*	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  *当数据库兼容级别设置为 90 或更高时，如果将 ANSI_WARNINGS 设置为 ON，则将使 ARITHABORT 隐式设置为 ON。如果数据库兼容级别设置为 80 或更低，则必须将 ARITHABORT 选项显式设置为 ON。

如果使用的是 OLE DB 或 ODBC 服务器连接，则唯一必须要修改的值是 ARITHABORT 设置。必须使用 sp_configure 在服务器级别或使用 SET 命令从应用程序中正确设置所有 DB-Library 值。有关 SET 选项的详细信息，请参阅使用 SQL Server 中的选项。

重要提示
极力建议在服务器的任一数据库中创建计算列的第一个索引视图或索引后，尽早在服务器范围内将 ARITHABORT 用户选项设置为 ON。

确定性函数

其他要求

筛选索引是一种经过优化的非聚集索引，适用于从表中选择少数行的查询。筛选索引使用筛选谓词对表中的部分数据进行索引。设计良好的筛选索引可以提高查询性能，降低存储成本和维护成本。

筛选索引所需的 SET 选项

有关空间索引的信息，请参阅 CREATE SPATIAL INDEX (Transact-SQL) 和使用空间索引（数据库引擎）。

有关 XML 索引的详细信息，请参阅 CREATE XML INDEX (Transact-SQL) 和 XML 数据类型列的索引。

索引键的最大大小为 900 字节。如果创建索引时，varchar 列中的现有数据未超过 900 字节，则可以对这些列创建超过 900 字节的索引；但是，以后在这些列上执行会导致总大小超过 900 字节的插入或更新操作时将失败。有关详细信息，请参阅索引键的最大大小。聚集索引的索引键不能包含在 ROW_OVERFLOW_DATA 分配单元中具有现有数据的 varchar 列。如果对 varchar 列创建了聚集索引，并且在 IN_ROW_DATA 分配单元中存在现有数据，则对该列执行的将数据推送到行外的后续插入或更新操作将会失败。有关分配单元的详细信息，请参阅表组织和索引组织。

非聚集索引可以在索引的叶级别包含非键列。计算索引键大小时，数据库引擎不考虑这些列。有关详细信息，请参阅具有包含列的索引。

注意
在对表进行分区时，如果分区键列尚未出现在非唯一聚集索引中，则它们将会由数据库引擎添加到索引中。索引列合并后的大小（不将包含列计算在内）加上任何添加的分区列在非唯一聚集索引中不能超过 1800 字节。

可以对计算列创建索引。此外，计算列可以具有 PERSISTED 属性。这意味着数据库引擎在表中存储计算值，并且在计算列所依赖的任何其他列发生更新时更新这些值。如果数据库引擎对列创建了索引并且该索引由某查询引用，则会使用这些持久值。

若要对计算列建立索引则该计算列必须具有确定性并精确。但是，使用 PERSISTED 属性会将可建立索引的计算列类型扩展为包含以下类型：

• 基于 Transact-SQL 和 CLR 函数以及由用户标记为确定性的 CLR 用户定义类型方法的计算列。

• 基于数据库引擎定义为确定性但不精确的表达式的计算列。

持久化计算列需要将以下 SET 选项设置为在上一部分“索引视图所需的 SET 选项”中显示的内容。

UNIQUE 或 PRIMARY KEY 约束只要满足所有索引条件，就可以包含计算列。具体来说，计算列必须具有确定性并精确，或者具有确定性并持久化。有关确定性的详细信息，请参阅确定性函数和不确定性函数。

可以对从 image、ntext、text、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 数据类型派生的计算列建立索引以作为键列或包含性非键列，条件是允许将该计算列数据类型作为索引键列或非键列。例如，不能对 xml 计算列创建主 XML 索引。如果索引键大小超过 900 字节，会显示一条警告消息。

对计算列创建索引可能导致之前正常运行的插入或更新操作失败。当计算列导致算术错误时可能产生这样的失败。例如，虽然下表中的计算列 c 将导致算术错误，但是 INSERT 语句仍有效。

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

相反，如果创建表之后对计算列 c 创建索引，则上述 INSERT 语句将失败。

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

有关详细信息，请参阅为计算列创建索引。

可以将非键列（称为包含列）添加到非聚集索引的叶级别，从而通过涵盖查询来提高查询性能。也就是说，查询中引用的所有列都作为键列或非键列包含在索引中。这样，查询优化器可以通过索引扫描找到所需的全部信息，而无需访问表或聚集索引数据。有关详细信息，请参阅具有包含列的索引。

SQL Server 2005 中引入了新的索引选项，还修改了指定选项的方式。在向后兼容的语法中，WITH option_name 等效于 WITH ( <option_name> = ON )。在设置索引选项时，下列规则适用：

• 只能使用 WITH (option_name= ON | OFF**)** 指定新的索引选项。

• 指定选项时不能在同一语句中同时使用向后兼容语法和新语法。例如，指定 WITH (DROP_EXISTING, ONLINE = ON**)** 会导致语句失败。

• 在创建 XML 索引时，必须使用 WITH (option_name= ON | OFF**)** 指定选项。

可使用 DROP_EXISTING 子句重新生成索引、添加或删除列、修改选项、修改列排序顺序或更改分区方案或文件组。

如果索引强制 PRIMARY KEY 或 UNIQUE 约束，且索引定义没有任何改变，则会删除并重新创建该索引并保留现有约束。不过，如果索引定义已改变，则该语句将失败。若要更改 PRIMARY KEY 或 UNIQUE 约束的定义，请删除该约束并添加具有新定义的约束。

为已经具有非聚集索引的表重建聚集索引时（使用相同或不同的键集），DROP_EXISTING 可以提高性能。DROP_EXISTING 代替先对旧的聚集索引执行 DROP INDEX 语句，然后再对新的聚集索引执行 CREATE INDEX 语句的过程。而是将重新生成一次非聚集索引，之后仅在索引定义已更改时再重新生成。如果索引定义与原始索引具有相同的索引名称、键列和分区列、唯一性属性以及排序顺序，则 DROP_EXISTING 子句不会重新生成非聚集索引。

无论是否重新生成非聚集索引，它们都将始终保留在其原始文件组或分区方案中，并使用原始的分区函数。如果聚集索引被重新生成到其他文件组或分区方案中，这些非聚集索引不会通过移动来与聚集索引的新位置保持一致。所以，即使非聚集索引以前与聚集索引对齐，现在可能也不再与其对齐。有关对齐已分区索引的详细信息，请参阅已分区索引的特殊指导原则。

如果以相同顺序使用相同索引键列，且具有相同升序和降序，则 DROP_EXISTING 子句不会重新对数据排序，除非索引语句指定非聚集索引且 ONLINE 选项设置为 OFF。如果聚集索引被禁用，则必须在 ONLINE 设置为 OFF 的情况下执行 CREATE INDEX WITH DROP_EXISTING 操作。如果非聚集索引被禁用且不与禁用的聚集索引关联，则可以在 ONLINE 设置为 OFF 或 ON 时执行 CREATE INDEX WITH DROP_EXISTING 操作。

删除或重新生成具有 128 个或更多区数的索引时，数据库引擎会将实际页释放及其关联的锁推迟到事务提交后。有关详细信息，请参阅删除并重新生成大型对象。

下列指南适用于联机执行索引操作：

• 不能在执行联机索引操作的过程中更改、截断或删除基础表。

• 索引操作期间需要额外的临时磁盘空间。有关详细信息，请参阅确定索引的磁盘空间要求。

• 可以对分区索引以及包含持久性计算列或包含列的索引执行联机操作。

有关详细信息，请参阅联机执行索引操作。

如果 ALLOW_ROW_LOCKS = ON 且 ALLOW_PAGE_LOCK = ON，则访问索引时允许行级、页级和表级锁。数据库引擎将选择相应的锁，并且可以将锁从行锁或页锁升级到表锁。有关详细信息，请参阅锁升级（数据库引擎）。

如果 ALLOW_ROW_LOCKS = OFF 且 ALLOW_PAGE_LOCK = OFF，则访问索引时仅允许使用表级锁。

有关为索引配置锁定粒度的详细信息，请参阅自定义索引的锁定。

若要返回有关索引的信息，可以使用目录视图、系统函数和系统存储过程。有关详细信息，请参阅查看索引信息。

数据压缩会在创建压缩表和索引主题中进行介绍。以下是要考虑的关键点：

• 通过压缩可将更多的行存储在页上，但不能更改最大行大小。

• 对索引的非叶页不会进行页压缩，但可进行行压缩。

• 每个非聚集索引都有单独的压缩设置，并且不会继承基础表的压缩设置。

• 对堆创建聚集索引时，聚集索引会继承该堆的压缩状态，除非指定了另一压缩状态。

以下限制适用于已分区索引：

• 如果表具有非对齐索引，则无法更改单个分区的压缩设置。

• ALTER INDEX <index> ...REBUILD PARTITION ... 语法可重新生成索引的指定分区。

• ALTER INDEX <index> ...REBUILD WITH ... 语法可重新生成索引的所有分区。

若要评估更改压缩状态将对表、索引或分区有何影响，请使用 sp_estimate_data_compression_savings 存储过程。

以下示例为 Purchasing.ProductVendor 表的 BusinessEntityID 列创建非聚集索引。

USE AdventureWorks2008R2;
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 (BusinessEntityID); 
GO

以下示例为 Sales.SalesPerson 表的 SalesQuota 和 SalesYTD 列创建非聚集组合索引。

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

以下示例为 Production.UnitMeasure 表的 Name 列创建唯一的非聚集索引。该索引将强制插入 Name 列中的数据具有唯一性。

USE AdventureWorks2008R2;
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.

以下示例首先在该选项设置为 ON 时在临时表中插入多行，然后在该选项设置为 OFF 时执行相同操作，以演示 IGNORE_DUP_KEY 选项的影响。单个行被插入 #Test 表，在执行第二个多行 INSERT 语句时将导致出现重复值。表中的行计数会返回插入的行数。

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

下面是第二个 INSERT 语句的结果。

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows

--------------

38

请注意，从 Production.UnitMeasure 表中插入的、不违反唯一性约束的行将成功插入。会发出警告并忽略重复行，但不会回滚整个事务。

将再次执行相同语句，但将 IGNORE_DUP_KEY 设置为 OFF。

USE AdventureWorks2008R2;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

下面是第二个 INSERT 语句的结果。

Server: Msg 2601, Level 14, State 1, Line 5

Cannot insert duplicate key row in object '#Test' with unique index

'AK_Index'. The statement has been terminated.

Number of rows

--------------

1

请注意，即使 Production.UnitMeasure 表中只有一行违反 UNIQUE 索引约束，也不会将其中任何一行插入该表。

以下示例使用 DROP_EXISTING 选项在 Production.WorkOrder 表的 ProductID 列上删除并重新创建现有索引。还设置了 FILLFACTOR 和 PAD_INDEX 选项。

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

以下示例将创建一个视图并为该视图创建索引。包含两个使用该索引视图的查询。

USE AdventureWorks2008R2;
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

以下示例创建具有一个键列 (PostalCode) 和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的非聚集索引。然后执行该索引覆盖的查询。若要显示查询优化器选择的索引，执行查询前请在 SQL Server Management Studio 中的**“查询”菜单上选择“显示实际执行计划”**。

USE AdventureWorks2008R2;
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';
GO

以下示例为现有分区方案 TransactionsPS1 创建非聚集分区索引。此示例假定安装了分区索引示例。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

下面的示例将对 Production.BillOfMaterials 表创建筛选索引。筛选谓词可包含那些不是筛选索引中的键列的列。本示例中的谓词将仅选择其中的 EndDate 为非 NULL 的行。

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

下面的示例将使用行压缩对无分区表创建索引。

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

下面的示例将通过对索引的所有分区使用行压缩来创建对已分区表的索引。

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

下面的示例将通过对索引的分区 1 使用页压缩并对索引的分区 2 至 4 使用行压缩来创建对已分区表的索引。

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO