Set Index Options

Article
06/13/2017

This topic describes how to modify the properties of an index in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

In This Topic

Before you begin:

Limitations and Restrictions

Security
To modify the properties of an index, using:

SQL Server Management Studio

Transact-SQL

Before You Begin

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

Permissions

Requires ALTER permission on the table or view.

Using SQL Server Management Studio

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 Savetable_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

Using Transact-SQL

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
    ) ;
GO

USE 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).

Share via