Skip to content

modify-an-index.md

Latest commit

 

History

History
78 lines (52 loc) · 3.62 KB

modify-an-index.md

File metadata and controls

78 lines (52 loc) · 3.62 KB
title Modify an Index | Microsoft Docs
ms.custom
ms.date 02/17/2017
ms.prod sql
ms.prod_service table-view-index, sql-database
ms.reviewer
ms.technology table-view-index
ms.topic conceptual
helpviewer_keywords
indexes [SQL Server], modifying
modifying indexes
index changes [SQL Server]
ms.assetid 97e3110d-fde7-4f5d-9309-dc1697960aeb
author MikeRayMSFT
ms.author mikeray
monikerRange = azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions

Modify an Index

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx_md]

This topic describes how to modify an index in [!INCLUDEssCurrent] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Important

Indexes created as the result of a PRIMARY KEY or UNIQUE constraint cannot be modified by using this method. Instead, the constraint must be modified.

In This Topic

Using SQL Server Management Studio

To modify an index

  1. In Object Explorer, connect to an instance of the [!INCLUDEssDEnoversion] and then expand that instance.

  2. Expand Databases, expand the database in which the table belongs, and then expand Tables.

  3. Expand the table in which the index belongs and then expand Indexes.

  4. Right-click the index that you want to modify and then click Properties.

  5. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.

To modify index columns

  1. To add, remove, or change the position of an index column, select the General page from the Index Properties dialog box.

Using Transact-SQL

To modify an index

The following example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table in the AdventureWorks database by using the DROP_EXISTING option. The options FILLFACTOR and PAD_INDEX are also set.

[!code-sqlIndexDDL#CreateIndex4]

The following example uses ALTER INDEX to set several options on the index AK_SalesOrderHeader_SalesOrderNumber.

[!code-sqlIndexDDL#AlterIndex4]

To modify index columns

  1. To add, remove, or change the position of an index column, you must drop and recreate the index.

See Also

CREATE INDEX (Transact-SQL)
ALTER INDEX (Transact-SQL)
INDEXPROPERTY (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
Set Index Options
Rename Indexes