Export (0) Print
Expand All
1 out of 4 rated this helpful - Rate this topic

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

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.

ms186872.note(en-US,SQL.90).gifNote:
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.

ms186872.note(en-US,SQL.90).gifNote:
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.

ms186872.note(en-US,SQL.90).gifNote:
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.

ms186872.note(en-US,SQL.90).gifNote:
If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.

Use index

Makes the index available.

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.