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 "ms188677.note(en-US,SQL.90).gif")
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 to duplicate key values in a multiple-row INSERT transaction on a unique clustered or unique nonclustered index.
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
Reorganizing and Rebuilding Indexes
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 Microsoft SQL Server 2005 Enterprise Edition.
No
Performing Index Operations Online.
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 2005 Enterprise Edition.
No
Configuring Parallel Index Operations
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 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
See Also
Concepts
Other Resources
sys.indexes (Transact-SQL)
sys.stats (Transact-SQL)