Index Properties (Options Page)

Use this page to view or modify properties for the selected index.

For additional information about how the index creation and maintenance options work, see Creating Indexes (Database Engine). For more information about rebuilding indexes, see CREATE INDEX (Transact-SQL) and ALTER INDEX (Transact-SQL).

Options

Drop existing index
Indicates when the dialog box needs to drop the pre-existing index and re-create the index with new properties. The Drop existing index check box only becomes selected if the dialog box is in the Recreate state. In this case, Rebuild index becomes unavailable.

Rebuild index
Rebuild the index. By default, the option is not selected when the dialog box is opened.

Ignore duplicate values
Specify whether a duplicate key value can be inserted into a column that is part of a unique clustered or nonclustered index. If selected, Microsoft SQL Server issues a warning when an INSERT statement is about to create a duplicate key and ignores the duplicate row. However, if cleared, SQL Server issues an error message and rolls back the INSERT operation. This option is not available if the index is not a unique index.

Automatically recompute statistics
Automatically updates the index statistics. This check box is selected by default.

Use row locks when accessing the index
Allow row-level locking. By default, SQL Server makes a choice of page-level, row-level, or table-level locking. When this check box is cleared, the index does not use row-level locking. By default, this check box is selected. This option is only available for SQL Server 2005 indexes. Clearing this option can speed up index maintenance, but it is more likely to block other users.

Note

It is usually better to let SQL Server manage the locking behavior.

Use page locks when accessing the index
Allow page-level locking. By default, SQL Server makes a choice of page-level, row-level, or table-level locking. When cleared, the index does not use page-level locking. By default, this check box is selected. This option is only available for SQL Server 2005 indexes. This option will reduce the chance of temporarily blocking other users, but it can slow down index maintenance actions.

Note

It is usually better to let SQL Server manage the locking behavior.

Store intermediate sort results in tempdb
Store intermediate sort results used to build the index in tempdb. By default, this check box is not selected and only becomes enabled if the dialog box is in the Rebuild or Recreate state.

Set fill factor
Specify how full SQL Server should make the leaf level of each index page when creating the index. Fill factor values can be from 1 through 100. The default value is read from database properties.

Pad index
Specify the space to leave open on each page in the intermediate levels of the index. Because Pad index is only useful when Set fill factor is specified, it is disabled unless Set fill factor is selected.

Allow online processing of DML statements while creating the index
Allows concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes during the index operation. This option is not selected by default and only becomes enabled if the dialog box is in the Rebuild or Recreate state. This option is only available for SQL Server 2005 indexes.

Selecting this check box reduces the impact upon users, but it can slow the completion of index maintenance actions.

Clearing this check box can speed up index maintenance, but it is more likely to block other users.

Note

This option is not available for XML indexes, or if the index is a disabled clustered index.

Set maximum degree of parallelism
Limit the number of processors to use during parallel plan execution. The default value, 0, uses the actual number of available CPUs. Setting the value to 1 suppresses parallel plan generation; setting the value to a number greater than 1 restricts the maximum number of processors used by a single query execution. This option only becomes available if the dialog box is in the Rebuild or Recreate state. This option is only available for SQL Server 2005 indexes.

Note

If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.

Filegroup
Select the filegroup where the index will be created. The drop-down list contains a list of the filegroups of the database. The default selection is the default filegroup of the database.