Setting Index Options
When you design, create, or modify an index, there are several index options to consider. These options can be specified when you first create an index or when you rebuild an index. Additionally, you can set some index options at any time by using the SET clause of the ALTER INDEX statement.
Setting stored in metadata
Sets the percentage of free space in the intermediate level pages during index creation.
Sets the percentage of free space in the leaf level of each index page during index creation.
Determines where the intermediate sort results, generated during index creation, are stored.
When ON, the sort results are stored in tempdb. When OFF, the sort results are stored in the filegroup or partition scheme in which the resulting index is stored.
If a sort operation is not required, or if the sort can be performed in memory, SORT_IN_TEMPDB is ignored.
Specifies the error response to duplicate key values in a multiple-row INSERT transaction on a unique clustered or unique nonclustered index.
Specifies whether out-of-date index statistics should be automatically recomputed.
Indicates the existing index should be dropped and recreated.
Determines whether concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes is allowed during index operations.
Online index operations are available only in Microsoft SQL Server 2005 Enterprise Edition.
Determines whether row locks are used in accessing index data.
Determines whether page locks are used in accessing index data.
Sets the maximum number of processors the query processor can use to execute a single index statement. Fewer processors may be used depending on the current system workload.
Parallel index operations are available only in SQL Server 2005 Enterprise Edition.
To set options on an index
Setting Options Without Rebuilding
By using the SET clause in the ALTER INDEX statement, you can set the following index options without rebuilding the index:
These options are immediately applied to the index. Other index options, such as FILLFACTOR and ONLINE, can be specified only when an index is created or rebuilt.
Viewing Index Option Settings
Not all index options values are stored in metadata. Those values that are stored in metadata can be viewed in the appropriate catalog views. To examine the current option settings on existing indexes, use the sys.indexes catalog view . To examine the current value for STATISTICS_NORECOMPUTE, use the sys.stats catalog view. For more information, see Viewing Index Information.
The following example sets the
IGNORE_DUP_KEY options for the
AK_Product_ProductNumber index on the
USE AdventureWorks; GO --Verify the current values for these options. SELECT allow_row_locks, ignore_dup_key FROM sys.indexes WHERE name = N'AK_Product_ProductNumber'; GO --Set the ALLOW_ROW_LOCKS option OFF and the IGNORE_DUP_KEY option ON. ALTER INDEX AK_Product_ProductNumber ON Production.Product SET (ALLOW_ROW_LOCKS = OFF, IGNORE_DUP_KEY = ON); GO --Verify the new values for these options. SELECT allow_row_locks, ignore_dup_key FROM sys.indexes WHERE name = N'AK_Product_ProductNumber'; GO