Set Index Options
This topic describes how to modify the properties of an index in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.
In This Topic
-
Before you begin:
-
To modify the properties of an index, using:
Limitations and Restrictions
-
The following options are immediately applied to the index by using the SET clause in the ALTER INDEX statement: ALLOW_PAGE_LOCKS, ALLOW_ROW_LOCKS, IGNORE_DUP_KEY, and STATISTICS_NORECOMPUTE.
-
The following options can be set when you rebuild an index by using either ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING: PAD_INDEX, FILLFACTOR, SORT_IN_TEMPDB, IGNORE_DUP_KEY, STATISTICS_NORECOMPUTE, ONLINE, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, MAXDOP, and DROP_EXISTING (CREATE INDEX only).
Security
To modify the properties of an index in Table Designer
-
In Object Explorer, click the plus sign to expand the database that contains the table on which you want to modify an index’s properties.
-
Click the plus sign to expand the Tables folder.
-
Right-click the table on which you want to modify an index’s properties and select Design.
-
On the Table Designer menu, click Indexes/Keys.
-
Select the index that you want to modify. Its properties will show up in the main grid.
-
Change the settings of any and all properties to customize the index.
-
Click Close.
-
On the File menu, select Save table_name.
To modify the properties of an index in Object Explorer
-
In Object Explorer, click the plus sign to expand the database that contains the table on which you want to modify an index’s properties.
-
Click the plus sign to expand the Tables folder.
-
Click the plus sign to expand the table on which you want to modify an index’s properties.
-
Click the plus sign to expand the Indexes folder.
-
Right-click the index of which you want to modify the properties and select Properties.
-
Under Select a page, select Options.
-
Change the settings of any and all properties to customize the index.
-
To add, remove, or change the position of an index column, select the General page from the Index Properties - index_name dialog box. For more information, see Index Properties F1 Help
To see the properties of all the indexes in a table
-
In Object Explorer, connect to an instance of Database Engine.
-
On the Standard bar, click New Query.
-
Copy and paste the following example into the query window and click Execute.
USE AdventureWorks2012; GO SELECT i.name AS index_name, i.type_desc, i.is_unique, ds.type_desc AS filegroup_or_partition_scheme, ds.name AS filegroup_or_partition_scheme_name, i.ignore_dup_key, i.is_primary_key, i.is_unique_constraint, i.fill_factor, i.is_padded, i.is_disabled, i.allow_row_locks, i.allow_page_locks, i.has_filter, i.filter_definition FROM sys.indexes AS i INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id WHERE is_hypothetical = 0 AND i.index_id <> 0 AND i.object_id = OBJECT_ID('HumanResources.Employee'); GO
To set the properties of an index
-
In Object Explorer, connect to an instance of Database Engine.
-
On the Standard bar, click New Query.
-
Copy and paste the following examples into the query window and click Execute.
USE AdventureWorks2012; GO ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON Sales.SalesOrderHeader SET ( STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = ON, ALLOW_PAGE_LOCKS = ON ) ; GOUSE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
For more information, see ALTER INDEX (Transact-SQL).