index_option (Transact-SQL)

Specifies a set of options that can be applied to an index that is part of a constraint definition that is created by using ALTER TABLE.

Transact-SQL Syntax Conventions

Syntax

{ 
    PAD_INDEX = { ON | OFF }
  | FILLFACTOR = fillfactor
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE  = { ON | OFF }
  | ALLOW_ROW_LOCKS= { ON | OFF }
  | ALLOW_PAGE_LOCKS={ ON | OFF }
  | SORT_IN_TEMPDB = { ON | OFF } 
  | ONLINE = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Arguments

• 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 enough space for at least one row of the maximum size the index can have, given the set of keys on the intermediate pages.

• 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 alteration. The value specified must be an integer value from 1 to 100. The default is 0.

  Note

  Fill factor values 0 and 100 are identical in all respects.

• IGNORE_DUP_KEY = { ON | OFF }
  Specifies the error response to duplicate key values in a multiple-row INSERT transaction 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.

  Note

  IGNORE_DUP_KEY has no effect in an UPDATE statement.

• STATISTICS_NORECOMPUTE = { ON | OFF }
  Specifies whether statistics are recomputed. The default is OFF.

  • ON
    Out-of-date statistics are not automatically recomputed.
  • OFF
    Automatic statistics updating are enabled.

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

• SORT_IN_TEMPDB = { ON | OFF }
  Specifies whether to store 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.

• 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

  Unique nonclustered indexes cannot be created online. This includes indexes that are created due to a UNIQUE or PRIMARY KEY constraint.

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

  Note

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

• MAXDOP = max_degree_of_parallelism
  Overrides the max degree of parallelism configuration option for the duration of the index operation. For more information, see max degree of parallelism Option. 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.
  • 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

For a complete description of index options, see CREATE INDEX (Transact-SQL).

See Also

Reference

ALTER TABLE (Transact-SQL)
column_constraint (Transact-SQL)
computed_column_definition (Transact-SQL)
table_constraint (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	New content: Added a note in the definition of the ONLINE option about unique nonclustered indexes.