INCLUDE (column [,... n ] )
Specifies the nonkey columns to be added to the leaf level of the nonclustered index.
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.
All data types are allowed except text, ntext, and image.
Index arguments
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.
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.
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.
The below table shows the possible max_degree_of_parallelism and its description.
Max_degree_of_parallelism Description
1 Suppresses parallel plan generation.
>1 Restricts the max.no of processors used in a parallel index operation to the specified number
0(Default) Uses the actual number of processors
2. Indexes in SQL Server 2008: Index features:
WHERE
Creates a filtered index by specifying which rows to include in the index.
The filtered index must be a nonclustered index on a table.
Filtered indexes do not apply to XML indexes and full-text indexes.
Filtered indexes do not allow the IGNORE_DUP_KEY option.
The filter predicate uses simple comparison logic and cannot reference a computed column, a UDT column, a spatial data type column, or a hierarchyID data type column. Comparisons using NULL literals are not allowed with the comparison operators.
FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | NULL}
Specifies the placement of FILESTREAM data for the table when a clustered index is created.
The FILESTREAM_ON clause allows FILESTREAM data to be moved to a different FILESTREAM filegroup or partition scheme.
filestream_filegroup_name is the name of a FILESTREAM filegroup.
The filegroup must have one file defined for the filegroup otherwise an error is raised.
If the table is partitioned, the FILESTREAM_ON clause must be included and must specify a partition scheme of FILESTREAM filegroups that uses the same partition function and partition columns as the partition scheme for the table. Otherwise, an error is raised.
If the table is not partitioned, the FILESTREAM column cannot be partitioned. FILESTREAM data for the table must be stored in a single filegroup that is specified in the FILESTREAM_ON clause.
FILESTREAM_ON NULL can be specified in a CREATE INDEX statement if a clustered index is being created and the table does not contain a FILESTREAM column.
Index arguments
DATA_COMPRESSION
Specifies the data compression option for the specified index, partition number, or range of partitions. The options are as follows:
NONE
Index or specified partitions are not compressed.
ROW
Index or specified partitions are compressed by using row compression.
PAGE
Index or specified partitions are compressed by using page compression.
