Modify an Index

Artykuł
12/08/2014

This topic describes how to modify an index in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

Ważne:
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:

SQL Server Management Studio

Transact-SQL

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.

Ikona strzałki używana z łączem Powrót na górę strony [Top]

Using Transact-SQL

To modify an index

Connect to the Database Engine.
From the Standard bar, click New Query.

Copy and paste the following example into the query window and click Execute. This example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table by using the DROP_EXISTING option. The options FILLFACTOR and PAD_INDEX are also set.

USE AdventureWorks2012;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

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

USE AdventureWorks2012;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO