Create INDEX

Create Relational Index
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON <object> ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column_name [ ,...n ] ) ]
[ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ ; ]

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

Create XML Index
CREATE [ PRIMARY ] XML INDEX index_name
ON <object> ( xml_column_name )
[ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH | PROPERTY } ] ]
[ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

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

<xml_index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| DROP_EXISTING = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| MAXDOP = max_degree_of_parallelism
}

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

Creates a unique index on a table or view. A unique index is one in which no two rows are permitted to have the same index key value. A clustered index on a view must be unique.

The SQL Server 2005 Database Engine does not allow creating a unique index on columns that already include duplicate values, whether or not IGNORE_DUP_KEY is set to ON. If this is tried, the Database Engine displays an error message. Duplicate values must be removed before a unique index can be created on the column or columns. Columns that are used in a unique index should be set to NOT NULL, because multiple null values are considered duplicates when a unique index is created. CLUSTERED

Creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. The bottom, or leaf, level of the clustered index contains the actual data rows of the table. A table or view is allowed one clustered index at a time. For more information, see Clustered Index Structures.

A view with a unique clustered index is called an indexed view. Creating a unique clustered index on a view physically materializes the view. A unique clustered index must be created on a view before any other indexes can be defined on the same view. For more information, see Designing Indexed Views.

Create the clustered index before creating any nonclustered indexes. Existing nonclustered indexes on tables are rebuilt when a clustered index is created.

If CLUSTERED is not specified, a nonclustered index is created.

Note:
Because the leaf level of a clustered index and the data pages are the same by definition, creating a clustered index and using the ON partition_scheme_name or ON filegroup_name clause effectively moves a table from the filegroup on which the table was created to the new partition scheme or filegroup. Before creating tables or indexes on specific filegroups, verify which filegroups are available and that they have enough empty space for the index. For more information, see Determining Index Disk Space Requirements.




NONCLUSTERED

Creates an index that specifies the logical ordering of a table. With a nonclustered index, the physical order of the data rows is independent of their indexed order. For more information, see Nonclustered Index Structures.

Each table can have up to 249 nonclustered indexes, regardless of how the indexes are created: either implicitly with PRIMARY KEY and UNIQUE constraints, or explicitly with CREATE INDEX.

For indexed views, nonclustered indexes can be created only on a view that has a unique clustered index already defined.

The default is NONCLUSTERED.index_name

Is the name of the index. Index names must be unique within a table or view but do not have to be unique within a database. Index names must follow the rules of identifiers.

Primary XML index names cannot start with the following characters: #, ##, @, or @@. column

Is the column or columns on which the index is based. Specify two or more column names to create a composite index on the combined values in the specified columns. List the columns to be included in the composite index, in sort-priority order, inside the parentheses after table_or_view_name.

Up to 16 columns can be combined into a single composite index key. All the columns in a composite index key must be in the same table or view. The maximum allowable size of the combined index values is 900 bytes. For more information about variable type columns in composite indexes, see the Remarks section.

Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be specified as key columns for an index. Also, a view definition cannot include ntext, text, or image columns, even if they are not referenced in the CREATE INDEX statement.

You can create indexes on CLR user-defined type columns if the type supports binary ordering. You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic and do not perform data access operations. For more information about indexing CLR user-defined type columns, see CLR User-defined Types. [ ASC | DESC ]

Determines the ascending or descending sort direction for the particular index column. The default is ASC.INCLUDE ( column [ ,... n ] )

Specifies the nonkey columns to be added to the leaf level of the nonclustered index. The nonclustered index can be unique or nonunique.

The maximum number of included nonkey columns is 1,023 columns; the minimum number is 1 column.

Column names cannot be repeated in the INCLUDE list and cannot be used simultaneously as both key and nonkey columns. For more information, see Index with Included Columns.

All data types are allowed except text, ntext, and image. The index must be created or rebuilt offline (ONLINE = OFF) if any one of the specified nonkey columns are varchar(max), nvarchar(max), or varbinary(max) data types.

Computed columns that are deterministic and either precise or imprecise can be included columns. Computed columns derived from image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml data types can be included nonkey columns as long as the computed column data types is allowable as an included column. For more information, see Creating Indexes on Computed Columns. ON partition_scheme_name ( column_name )

Specifies the partition scheme that defines the filegroups onto which the partitions of a partitioned index will be mapped. The partition scheme must exist within the database by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. column_name specifies the column against which a partitioned index will be partitioned. This column must match the data type, length, and precision of the argument of the partition function that partition_scheme_name is using. column_name is not restricted to the columns in the index definition. Any column in the base table can be specified, except when partitioning a UNIQUE index, column_name must be chosen from among those used as the unique key. This restriction allows the Database Engine to verify uniqueness of key values within a single partition only.

Note:
When you partition a nonunique, clustered index, the Database Engine by default adds the partitioning column to the list of clustered index keys, if it is not already specified. When partitioning a nonunique, nonclustered index, the Database Engine adds the partitioning column as a nonkey (included) column of the index, if it is not already specified.






If partition_scheme_name or filegroup is not specified and the table is partitioned, the index is placed in the same partition scheme, using the same partitioning column, as the underlying table.

You cannot specify a partitioning scheme on an XML index. If the base table is partitioned, the XML index uses the same partition scheme as the table.

For more information about partitioning indexes, see Special Guidelines for Partitioned Indexes.ON filegroup_name

Creates the specified index on the specified filegroup. If no location is specified and the table or view is not partitioned, the index uses the same filegroup as the underlying table or view. The filegroup must already exist. XML indexes use the same filegroup as the table.ON "default"

Creates the specified index on the default filegroup.

The term default, in this context, is not a keyword. It is an identifier for the default filegroup and must be delimited, as in ON "default" or ON [default]. If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. This is the default setting. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).[PRIMARY] XML

Creates an XML index on the specified xml column. When PRIMARY is specified, a clustered index is created with the clustered key formed from the clustering key of the user table and an XML node identifier. Each table can have up to 249 XML indexes. Note the following when you create an XML index:
A clustered index must exist on the primary key of the user table.

The clustering key of the user table is limited to 15 columns.

Each xml column in a table can have one primary XML index and multiple secondary XML indexes.

A primary XML index on an xml column must exist before a secondary XML index can be created on the column.

An XML index can only be created on a single xml column. You cannot create an XML index on a non-xml column, nor can you create a relational index on an xml column.

You cannot create an XML index, either primary or secondary, on an xml column in a view, on a table-valued variable with xml columns, or xml type variables.

You cannot create a primary XML index on a computed xml column.

The SET option settings must be the same as those required for indexed views and computed column indexes. Specifically, the option ARITHABORT must be set to ON when an XML index is created and when inserting, deleting, or updating values in the xml column. For more information, see SET Options That Affect Results.


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

Is the xml column on which the index is based. Only one xml column can be specified in a single XML index definition; however, multiple secondary XML indexes can be created on an xml column.USING XML INDEX xml_index_name

Specifies the primary XML index to use in creating a secondary XML index. FOR { VALUE | PATH | PROPERTY }

Specifies the type of secondary XML index. VALUE

Creates a secondary XML index on columns where key columns are (node value and path) of the primary XML index.PATH

Creates a secondary XML index on columns built on path values and node values in the primary XML index. In the PATH secondary index, the path and node values are key columns that allow efficient seeks when searching for paths. PROPERTY

Creates a secondary XML index on columns (PK, path and node value) of the primary XML index where PK is the primary key of the base table.

<object>::=


Is the fully qualified or nonfully qualified object to be indexed.database_name

Is the name of the database.schema_name

Is the name of the schema to which the table or view belongs.table_or_view_name

Is the name of the table or view to be indexed.

The view must be defined with SCHEMABINDING to create an index on it. A unique clustered index must be created on a view before any nonclustered index is created. For more information about indexed views, see the Remarks section.
<relational_index_option>::=


Specifies the options to use when you create the index.PAD_INDEX = { ON | OFF }

Specifies index padding. The default is OFF.ON

The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.OFF or fillfactor is not specified

The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. If the percentage specified for FILLFACTOR is not large enough to allow for one row, the Database Engine internally overrides the percentage to allow for the minimum. The number of rows on an intermediate index page is never less than two, regardless of how low the value of fillfactor.

In backward compatible syntax, WITH PAD_INDEX is equivalent to WITH PAD_INDEX = ON. FILLFACTOR = fillfactor

Specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild. fillfactor must be an integer value from 1 to 100. The default is 0. If fillfactor is 100 or 0, the Database Engine creates indexes with leaf pages filled to capacity.

Note:
Fill factor values 0 and 100 are the same in all respects.






The FILLFACTOR setting applies only when the index is created or rebuilt. The Database Engine does not dynamically keep the specified percentage of empty space in the pages. To view the fill factor setting, use the sys.indexes catalog view.

Important:
Creating a clustered index with a FILLFACTOR less than 100 affects the amount of storage space the data occupies because the Database Engine redistributes the data when it creates the clustered index.






For more information, see Fill Factor. SORT_IN_TEMPDB = { ON | OFF }

Specifies whether to store temporary sort results in tempdb. The default is OFF.ON

The intermediate sort results that are used to build the index are stored in tempdb. This may reduce the time required to create an index if tempdb is on a different set of disks than the user database. However, this increases the amount of disk space that is used during the index build. OFF

The intermediate sort results are stored in the same database as the index.

In addition to the space required in the user database to create the index, tempdb must have about the same amount of additional space to hold the intermediate sort results. For more information, see tempdb and Index Creation.

In backward compatible syntax, WITH SORT_IN_TEMPDB is equivalent to WITH SORT_IN_TEMPDB = ON. IGNORE_DUP_KEY = { ON | OFF }

Specifies the error response to duplicate key values in a multiple-row insert operation on a unique clustered or unique nonclustered index. The default is OFF.ON

A warning message is issued and only the rows violating the unique index fail. OFF

An error message is issued and the entire INSERT transaction is rolled back.

The IGNORE_DUP_KEY setting applies only to insert operations that occur after the index is created or rebuilt. The setting has no effect during the index creation operation.

IGNORE_DUP_KEY cannot be set to ON for XML indexes and indexes created on a view.

In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON. STATISTICS_NORECOMPUTE = { ON | OFF }

Specifies whether distribution statistics are recomputed. The default is OFF.ON

Out-of-date statistics are not automatically recomputed.OFF

Automatic statistics updating are enabled.

To restore automatic statistics updating, set the STATISTICS_NORECOMPUTE to OFF, or execute UPDATE STATISTICS without the NORECOMPUTE clause.

Important:
Disabling automatic recomputation of distribution statistics may prevent the query optimizer from picking optimal execution plans for queries involving the table.






In backward compatible syntax, WITH STATISTICS_NORECOMPUTE is equivalent to WITH STATISTICS_NORECOMPUTE = ON. DROP_EXISTING = { ON | OFF }

Specifies that the named, preexisting clustered, nonclustered, or XML index is dropped and rebuilt. The default is OFF.ON

The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options. OFF

An error is displayed if the specified index name already exists.

The index type, relational or XML, cannot be changed by using DROP_EXISTING. Also, a primary XML index cannot be redefined as a secondary XML index, or vice versa.

In backward compatible syntax, WITH DROP_EXISTING is equivalent to WITH DROP_EXISTING = ON. ONLINE = { ON | OFF }

Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.

Note:
Online index operations are available only in SQL Server 2005 Enterprise Edition.




ON

Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to proceed. At the start of the operation, a Shared (S) lock is held on the source object for a very short period of time. At the end of the operation, for a short period of time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.OFF

Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

For more information, see How Online Index Operations Work. For more information about locks, see Lock Modes.

Indexes, including indexes on global temp tables, can be created online with the following exceptions:
XML index.

Index on a local temp table.

Initial unique clustered index on a view.

Disabled clustered indexes.

Clustered index if the underlying table contains LOB data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.

Nonclustered index defined with LOB data type columns.


Note:
A nonunique nonclustered index can be created online if the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.






For more information, see Performing Index Operations Online. ALLOW_ROW_LOCKS = { ON | OFF }

Specifies whether row locks are allowed. The default is ON.ON

Row locks are allowed when accessing the index. The Database Engine determines when row locks are used. OFF

Row locks are not used.

ALLOW_PAGE_LOCKS = { ON | OFF }

Specifies whether page locks are allowed. The default is ON.ON

Page locks are allowed when accessing the index. The Database Engine determines when page locks are used.OFF

Page locks are not used.

MAXDOP = max_degree_of_parallelism

Overrides the max degree of parallelism configuration option for the duration of the index operation. Use MAXDOP to limit the number of processors used in a parallel plan execution. The maximum is 64 processors.

max_degree_of_parallelism can be:1

Suppresses parallel plan generation.>1

Restricts the maximum number of processors used in a parallel index operation to the specified number or fewer based on the current system workload.0 (default)

Uses the actual number of processors or fewer based on the current system workload.

For more information, see Configuring Parallel Index Operations.

Note:
Parallel index operations are available only in SQL Server 2005 Enterprise Edition.





Remarks



The CREATE INDEX statement is optimized like any other query. To save on I/O operations, the query processor may choose to scan another index instead of performing a table scan. The sort operation may be eliminated in some situations. On multiprocessor computers with SQL Server 2005 Enterprise Edition, CREATE INDEX can use more processors to perform the scan and sort operations associated with creating the index, in the same way as other queries do. For more information, see Configuring Parallel Index Operations.

The create index operation can be minimally logged if the database recovery model is set to either bulk-logged or simple. For more information, see Choosing a Recovery Model for Index Operations.

Indexes can be created on a temporary table. When the table is dropped or the session ends, the indexes are dropped.

Indexes support extended properties. For more information, see Using Extended Properties on Database Objects.
Clustered Indexes


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


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.
Indexed Views


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:
Verify the SET options are correct for all existing tables that will be referenced in the view.

Verify the SET options for the session are set correctly before creating any new tables and the view.

Verify the view definition is deterministic.

Create the view by using the WITH SCHEMABINDING option.

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


The definition of an indexed view must be deterministic. A view is deterministic if all expressions in the select list, as well as the WHERE and GROUP BY clauses, are deterministic. Deterministic expressions always return the same result any time they are evaluated with a specific set of input values. Only deterministic functions can participate in deterministic expressions. For example, the DATEADD function is deterministic because it always returns the same result for any given set of argument values for its three parameters. GETDATE is not deterministic because it is always invoked with the same argument, but the value it returns changes each time it is executed. For more information, see Deterministic and Nondeterministic Functions.

Even if an expression is deterministic, if it contains float expressions, the exact result may depend on the processor architecture or version of microcode. To ensure data integrity, such expressions can participate only as nonkey columns of indexed views. Deterministic expressions that do not contain float expressions are called precise. Only precise deterministic expressions can participate in key columns and in WHERE or GROUP BY clauses of indexed views.

Use the IsDeterministic property of the COLUMNPROPERTY function to determine whether a view column is deterministic. Use the IsPrecise property of the COLUMNPROPERTY function to determine if a deterministic column in a view with schema binding is precise. COLUMNPROPERTY returns 1 if TRUE, 0 if FALSE, and NULL for input that is not valid. This means the column is not deterministic or not precise.
Additional Requirements


In addition to the SET options and deterministic function requirements, the following requirements must be met:
The user that executes CREATE INDEX must be the owner of the view.

If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Base tables must have the correct SET options set at the time the table is created or it cannot be referenced by the view with schema binding.

Tables must be referenced by two-part names, schema.tablename, in the view definition.

User-defined functions must be created by using the WITH SCHEMABINDING option.

User-defined functions must be referenced by two-part names, schema.function.

The view must be created by using the WITH SCHEMABINDING option.

The view must reference only base tables in the same database, not other views.

The view definition must not contain the following:




COUNT(*)

ROWSET function


Derived table

self-join


DISTINCT

STDEV, VARIANCE, AVG


float*, text, ntext, or image columns

Subquery


full-text predicates (CONTAIN, FREETEXT)

SUM on nullable expression


CLR user-defined aggregate function

TOP


MIN, MAX

UNION
*The indexed view can contain float columns; however, such columns cannot be included in the clustered index key.


If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.

Indexed views can be created on a partitioned table, and can themselves be partitioned. For more information about partitioning, see the previous section "Partitioned Indexes".

To prevent the Database Engine from using indexed views, include the OPTION (EXPAND VIEWS) hint on the query. Also, if any of the listed options are incorrectly set, this will prevent the optimizer from using the indexes on the views. For more information about the OPTION (EXPAND VIEWS) hint, see SELECT (Transact-SQL).

The compatibility level of the database cannot be less than 80. A database containing an indexed view cannot be changed to a compatibility level lower than 80.
XML Indexes


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


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

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

Copy
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.
Included Columns in Indexes


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.
Specifying Index Options


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

DROP_EXISTING Clause


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.
ONLINE Option


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.
Row and Page Locks Options


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.