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.
Index option |
Description |
Setting stored in metadata |
Related topic |
|---|---|---|---|
PAD_INDEX |
Sets the percentage of free space in the intermediate level pages during index creation. |
Yes |
|
FILLFACTOR |
Sets the percentage of free space in the leaf level of each index page during index creation. |
Yes |
|
SORT_IN_TEMPDB |
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. .gif "Note") Note
If a sort operation is not required, or if the sort can be performed in memory, SORT_IN_TEMPDB is ignored.
|
No |
|
IGNORE_DUP_KEY |
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. |
Yes |
|
STATISTICS_NORECOMPUTE |
Specifies whether out-of-date index statistics should be automatically recomputed. |
Yes |
|
DROP_EXISTING |
Indicates the existing index should be dropped and recreated. |
No |
|
ONLINE |
Determines whether concurrent user access to the underlying table or clustered index data and any associated nonclustered indexes is allowed during index operations. Note
Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.
|
No |
|
ALLOW_ROW_LOCKS |
Determines whether row locks are used in accessing index data. |
Yes |
|
ALLOW_PAGE_LOCKS |
Determines whether page locks are used in accessing index data. |
Yes |
|
MAXDOP |
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. Note
Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.
|
No |
|
DATA_COMPRESSION |
Specifies the data compression option for the specified table, partition number or range of partitions. Options are NONE, ROW, and PAGE. |
Yes |
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:
ALLOW_PAGE_LOCKS
ALLOW_ROW_LOCKS
IGNORE_DUP_KEY
STATISTICS_NORECOMPUTE
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.
Examples
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 AdventureWorks2008R2;
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