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

Fill Factor

FILLFACTOR

Sets the percentage of free space in the leaf level of each index page during index creation.

Yes

Fill Factor

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.

NoteNote
If a sort operation is not required, or if the sort can be performed in memory, SORT_IN_TEMPDB is ignored.

No

tempdb and Index Creation

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

CREATE INDEX

STATISTICS_NORECOMPUTE

Specifies whether out-of-date index statistics should be automatically recomputed.

Yes

Index Statistics

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.

NoteNote
Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

No

Performing Index Operations Online.

ALLOW_ROW_LOCKS

Determines whether row locks are used in accessing index data.

Yes

CREATE INDEX

Customizing Locking for an Index

ALLOW_PAGE_LOCKS

Determines whether page locks are used in accessing index data.

Yes

CREATE INDEX

Customizing Locking for an Index

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.

NoteNote
Parallel index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.

No

Modifying Indexes

DATA_COMPRESSION

Specifies the data compression option for the specified table, partition number or range of partitions. Options are NONE, ROW, and PAGE.

Yes

Creating Compressed Tables and Indexes

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