Modify an Index
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This topic describes how to modify an index in SQL Server by using SQL Server Management Studio or Transact-SQL.
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
To modify an index, using:
Using SQL Server Management Studio
To modify an index
In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
Expand Databases, expand the database in which the table belongs, and then expand Tables.
Expand the table in which the index belongs and then expand Indexes.
Right-click the index that you want to modify and then click Properties.
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
- 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.
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
ON Production.WorkOrder(ProductID)
WITH (FILLFACTOR = 80,
PAD_INDEX = ON,
DROP_EXISTING = ON)
;
The following example uses ALTER INDEX to set several options on the index AK_SalesOrderHeader_SalesOrderNumber
.
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
Sales.SalesOrderHeader
SET (
STATISTICS_NORECOMPUTE = ON,
IGNORE_DUP_KEY = ON,
ALLOW_PAGE_LOCKS = ON
)
;
To modify index columns
- 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