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 when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The default is OFF.
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 SQL Server Enterprise, Developer, and Evaluation editions.
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 Enterprise, Developer, and Evaluation editions.
Specifies the data compression option for the specified table, partition number or range of partitions. Options are NONE, ROW, and PAGE.
To set options on an index
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.
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 ALLOW_ROW_LOCKS and IGNORE_DUP_KEY options for the AK_Product_ProductNumber index on the Production.Product table.
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