Skip to content

Latest commit

 

History

History
122 lines (94 loc) · 4.88 KB

set-index-options.md

File metadata and controls

122 lines (94 loc) · 4.88 KB
titledescriptionauthorms.authorms.datems.servicems.subservicems.topicms.customhelpviewer_keywordsmonikerRange
Set Index Options
Set Index Options
MikeRayMSFT
mikeray
06/26/2019
sql
table-view-index
how-to
ignite-2024
ALLOW_ROW_LOCKS option
SORT_IN_TEMPDB option
DROP_EXISTING clause
large data, indexes
PAD_INDEX
STATISTICS_NORECOMPUTE
MAXDOP index option, setting
index options [SQL Server]
MAXDOP index option
IGNORE_DUP_KEY option
ALLOW_PAGE_LOCKS option
OPTIMIZE_FOR_SEQUENTIAL_KEY option
ONLINE
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

Set Index Options

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance FabricSQLDB]

This topic describes how to modify the properties of an index in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

In This Article

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, OPTIMIZE_FOR_SEQUENTIAL_KEY, 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

  1. 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.
  2. Click the plus sign to expand the Tables folder.
  3. Right-click the table on which you want to modify an index's properties and select Design.
  4. On the Table Designer menu, click Indexes/Keys.
  5. Select the index that you want to modify. Its properties will show up in the main grid.
  6. Change the settings of any and all properties to customize the index.
  7. Click Close.
  8. On the File menu, select Savetable_name.

To modify the properties of an index in Object Explorer

  1. 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.
  2. Click the plus sign to expand the Tables folder.
  3. Click the plus sign to expand the table on which you want to modify an index's properties.
  4. Click the plus sign to expand the Indexes folder.
  5. Right-click the index of which you want to modify the properties and select Properties.
  6. Under Select a page, select Options.
  7. Change the settings of any and all properties to customize the index.
  8. 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

The following example shows the properties of all indexes in a table in the AdventureWorks database.

SELECTi.nameAS index_name , i.type_desc , i.is_unique , ds.type_descAS filegroup_or_partition_scheme , ds.nameAS 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_definitionFROMsys.indexesAS i INNER JOINsys.data_spacesAS ds ONi.data_space_id=ds.data_space_idWHERE is_hypothetical =0ANDi.index_id<>0ANDi.object_id= OBJECT_ID('HumanResources.Employee') ;

To set the properties of an index

The following examples set the properties of indexes in the AdventureWorks database.

:::code language="sql" source="codesnippet/tsql/set-index-options_1.sql":::

:::code language="sql" source="codesnippet/tsql/set-index-options_2.sql":::

For more information, see ALTER INDEX (Transact-SQL).

close