Creating a clustered index on a table (heap) or dropping and re-creating an existing clustered index requires additional workspace to be available in the database to accommodate data sorting and a temporary copy of the original table or existing clustered index data. For more information, see Determining Index Disk Space Requirements. For more information about clustered indexes, see Creating Clustered Indexes.

When a unique index exists, the Database Engine checks for duplicate values each time data is added by a insert operations. Insert operations that would generate duplicate key values are rolled back, and the Database Engine displays an error message. This is true even if the insert operation changes many rows but causes only one duplicate. If an attempt is made to enter data for which there is a unique index and the IGNORE_DUP_KEY clause is set to ON, only the rows violating the UNIQUE index fail. For more information about unique indexes, see Creating Unique Indexes.

Partitioned indexes are created and maintained in a similar manner to partitioned tables, but like ordinary indexes, they are handled as separate database objects. You can have a partitioned index on a table that is not partitioned, and you can have a nonpartitioned index on a table that is partitioned.

If you are creating an index on a partitioned table, and do not specify a filegroup on which to place the index, the index is partitioned in the same manner as the underlying table. This is because indexes, by default, are placed on the same filegroups as their underlying tables, and for a partitioned table in the same partition scheme that uses the same partitioning columns.

When partitioning a nonunique, clustered index, the Database Engine by default adds any partitioning columns to the list of clustered index keys, if not already specified.

Indexed views can be created on partitioned tables in the same manner as indexes on tables. For more information about partitioned indexes, see Partitioned Tables and Indexes.

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution. The view does not have to be referenced in the query for the optimizer to consider that view for a substitution.

The following steps are required to create an indexed view are critical to the successful implementation of the view:

1. Verify the SET options are correct for all existing tables that will be referenced in the view.
   
2. Verify the SET options for the session are set correctly before creating any new tables and the view.
   
3. Verify the view definition is deterministic.
   
4. Create the view by using the WITH SCHEMABINDING option.
   
5. Create the unique clustered index on the view.
   

Required SET Options for Indexed Views

Evaluating the same expression can produce different results in the Database Engine if different SET options are active when the query is executed. For example, after the SET option CONCAT_NULL_YIELDS_NULL is set to ON, the expression 'abc' + NULL returns the value NULL. However, after CONCAT_NULL_YIEDS_NULL is set to OFF, the same expression produces 'abc'.

To make sure that the views can be maintained correctly and return consistent results, indexed views require fixed values for several SET options. The SET options in the following table must be set to the values shown in the Required Value column whenever the following conditions occur:

• The indexed view is created.
  
• There is any insert, update, or delete operation performed on any table that participates in the indexed view. This includes operations such as bulk copy, replication, and distributed queries.
  
• The indexed view is used by the query optimizer to produce the query plan.
  

  SET options	Required value	Default server value	Default OLE DB and ODBC value	Default DB-Library value
  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

  *In SQL Server 2005, setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must explicitly be set to ON.
  

If you are using an OLE DB or ODBC server connection, the only value that must be modified is the ARITHABORT setting. All DB-Library values must be set correctly either at the server level by using sp_configure or from the application by using the SET command. For more information about SET options, see Using Options in SQL Server.

Important:
We strongly recommend that the ARITHABORT user option be set server-wide to ON as soon as the first indexed view or index on a computed column is created in any database on the server.

Deterministic Functions

Additional Requirements

For more information see, Indexes on xml Data Type Columns.

The maximum size for an index key is 900 bytes. Indexes on varchar columns that exceed 900 bytes can be created if the existing data in the columns do not exceed 900 bytes at the time the index is created; however, subsequent insert or update actions on the columns that cause the total size to be greater than 900 bytes will fail. For more information, see Maximum Size of Index Keys. The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail. For more information about allocation units, see Table and Index Organization.

In SQL Server 2005, nonclustered indexes can include nonkey columns in the leaf level of the index. These columns are not considered by the Database Engine when calculating the index key size . For more information, see Index with Included Columns.

Indexes can be created on computed columns. In SQL Server 2005, computed columns can have the property PERSISTED. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query.

To index a computed column, the computed column must deterministic and precise. However, using the PERSISTED property expands the type of indexable computed columns to include:

• Computed columns based on Transact-SQL and CLR functions and CLR user-defined type methods that are marked deterministic by the user.
  
• Computed columns based on expressions that are deterministic as defined by the Database Engine but imprecise.
  

Persisted computed columns require the following SET options to be set as shown in the previous section "Required SET Options for Indexed Views".

The UNIQUE or PRIMARY KEY constraint can contain a computed column as long as it satisfies all conditions for indexing. Specifically, the computed column must be deterministic and precise or deterministic and persisted. For more information about determinism, see Deterministic and Nondeterministic Functions.

Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be indexed either as a key or included nonkey column as long as the computed column data type is allowable as an index key column or nonkey column. For example, you cannot create a primary XML index on a computed xml column. If the index key size exceeds 900 bytes, a warning message is displayed.

Creating an index on a computed column may cause the failure of an insert or update operation that previously worked. Such a failure may take place when the computed column results in arithmetic error. For example, in the following table, although computed column c results in an arithmetic error, the INSERT statement works.

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

If, instead, after creating the table, you create an index on computed column c, the same INSERT statement will now fail.

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

For more information, see Creating Indexes on Computed Columns.

Nonkey columns, called included columns, can be added to the leaf level of a nonclustered index to improve query performance by covering the query. That is, all columns referenced in the query are included in the index as either key or nonkey columns. This allows the query optimizer to locate all the required information from an index scan; the table or clustered index data is not accessed. For more information, see Index with Included Columns.

SQL Server 2005 introduces new index options and also modifies the way in which options are specified. In backward compatible syntax, WITH option_name is equivalent to WITH ( <option_name> = ON ). When you set index options, the following rules apply:

• New index options can only be specified by using WITH (option_name = ON | OFF).
  
• Options cannot be specified by using both the backward compatible and new syntax in the same statement. For example, specifying WITH (DROP_EXISTING, ONLINE = ON) causes the statement to fail.
  
• When you create an XML index, the options must be specified by using WITH (option_name = ON | OFF).
  

You can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup.

If the index enforces a PRIMARY KEY or UNIQUE constraint and the index definition is not altered in any way, the index is dropped and re-created preserving the existing constraint. However, if the index definition is altered the statement fails. To change the definition of a PRIMARY KEY or UNIQUE constraint, drop the constraint and add a constraint with the new definition.

DROP_EXISTING enhances performance when you re-create a clustered index, with either the same or different set of keys, on a table that also has nonclustered indexes. DROP_EXISTING replaces the execution of a DROP INDEX statement on the old clustered index followed by the execution of a CREATE INDEX statement for the new clustered index. The nonclustered indexes are rebuilt once, and then only if the index definition has changed. The DROP_EXISTING clause does not rebuild the nonclustered indexes when the index definition has the same index name, key and partition columns, uniqueness attribute, and sort order as the original index.

Whether the nonclustered indexes are rebuilt or not, they always remain in their original filegroups or partition schemes and use the original partition functions. If a clustered index is rebuilt to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the clustered index. Therefore, even the nonclustered indexes previously aligned with the clustered index, they may no longer be aligned with it. For more information about partitioned index alignment, see Special Guidelines for Partitioned Indexes.

The DROP_EXISTING clause will not sort the data again if the same index key columns are used in the same order and with the same ascending or descending order, unless the index statement specifies a nonclustered index and the ONLINE option is set to OFF. If the clustered index is disabled, the CREATE INDEX WITH DROP_EXISTING operation must be performed with ONLINE set to OFF. If a nonclustered index is disabled and is not associated with a disabled clustered index, the CREATE INDEX WITH DROP_EXISTING operation can be performed with ONLINE set to OFF or ON.

When indexes with 128 extents or more are dropped or rebuilt, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. For more information, see Dropping and Rebuilding Large Objects.

The following guidelines apply for performing index operations online:

• The underlying table cannot be altered, truncated, or dropped while an online index operation is in process.
  
• Additional temporary disk space is required during the index operation. For more information, see Determining Index Disk Space Requirements.
  
• Online operations can be performed on partitioned indexes and indexes that contain persisted computed columns, or included columns.
  

For more information, see Performing Index Operations Online.

When ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCK = ON, row-, page-, and table-level locks are allowed when accessing the index. The Database Engine chooses the appropriate lock and can escalate the lock from a row or page lock to a table lock. For more information, see Lock Escalation (Database Engine).

When ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCK = OFF, only a table-level lock is allowed when accessing the index.

For more information about configuring the locking granularity for an index, see Customizing Locking for an Index.

To return information about indexes, you can use catalog views, system functions, and system stored procedures. For more information, see Viewing Index Information.

The following example creates a nonclustered index on the VendorID column of the Purchasing.ProductVendor table.

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

The following example creates a nonclustered composite index on the SalesQuota and SalesYTD columns of the Sales.SalesPerson table.

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

The following example creates a unique nonclustered index on the Name column of the Production.UnitMeasure table. The index will enforce uniqueness on the data inserted into the Name column.

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

The following query tests the uniqueness constraint by attempting to insert a row with the same value as that in an existing row.

--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());

The resulting error message is:

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.

The following example demonstrates the effect of the IGNORE_DUP_KEY option by inserting multiple rows into a temporary table first with the option set to ON and again with the option set to OFF. A single row is inserted into the #Test table that will intentionally cause a duplicate value when the second multiple-row INSERT statement is executed. A count of rows in the table returns the number of rows inserted.

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

Here are the results of the second INSERT statement.

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

Number of rows 
-------------- 
38

Notice that the rows inserted from the Production.UnitMeasure table that did not violate the uniqueness constraint were successfully inserted. A warning was issued and the duplicate row ignored, but the entire transaction was not rolled back.

The same statements are executed again, but with IGNORE_DUP_KEY set to OFF.

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

Here are the results of the second INSERT statement.

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

Notice that none of the rows from the Production.UnitMeasure table were inserted into the table even though only one row in the table violated the UNIQUE index constraint.

The following example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table by using the DROP_EXISTING option. The options FILLFACTOR and PAD_INDEX are also set.

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

The following example creates a view and an index on that view. Two queries are included that use the indexed view.

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

The following example creates a nonclustered index with one key column (PostalCode) and four nonkey columns (AddressLine1, AddressLine2, City, StateProvinceID). A query that is covered by the index follows. To display the index that is selected by the query optimizer, on the Query menu in SQL Server Management Studio, select Display Actual Execution Plan before executing the query.

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

The following example creates a primary XML index on the CatalogDescription column in the Production.ProductModel table.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

The following example creates a secondary XML index on the CatalogDescription column in the Production.ProductModel table.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

The following example creates a nonclustered partitioned index on TransactionsPS1, an existing partition scheme. This example assumes the partitioned index sample has been installed. For installation information, see Readme_PartitioningScript.

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