Delete an Index

This topic describes how to delete (drop) an index in SQL Server 2012 by using SQL Server Management Studio or Transact-SQL.

In This Topic

  • Before you begin:

    Limitations and Restrictions

    Security

  • To delete an index, using:

    SQL Server Management Studio

    Transact-SQL

Before You Begin

Limitations and Restrictions

Indexes created as the result of a PRIMARY KEY or UNIQUE constraint cannot be deleted by using this method. Instead, the constraint must be deleted. To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause in Transact-SQL. For more information, see Delete Primary Keys.

Security

Permissions

Requires ALTER permission on the table or view. This permission is granted by default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles.

Arrow icon used with Back to Top link [Top]

Using SQL Server Management Studio

To delete an index by using Object Explorer

  1. In Object Explorer, expand the database that contains the table on which you want to delete an index.

  2. Expand the Tables folder.

  3. Expand the table that contains the index you want to delete.

  4. Expand the Indexes folder.

  5. Right-click the index you want to delete and select Delete.

  6. In the Delete Object dialog box, verify that the correct index is in the Object to be deleted grid and click OK.

To delete an index using Table Designer

  1. In Object Explorer, expand the database that contains the table on which you want to delete an index.

  2. Expand the Tables folder.

  3. Right-click the table that contains the index you want to delete and click Design.

  4. On the Table Designer menu, click Indexes/Keys.

  5. In the Indexes/Keys dialog box, select the index you want to delete.

  6. Click Delete.

  7. Click Close.

  8. On the File menu, select Save table_name.

Arrow icon used with Back to Top link [Top]

Using Transact-SQL

To delete an index

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;
    GO
    -- delete the IX_ProductVendor_BusinessEntityID index
    -- from the Purchasing.ProductVendor table
    DROP INDEX IX_ProductVendor_BusinessEntityID 
        ON Purchasing.ProductVendor;
    GO
    

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

Arrow icon used with Back to Top link [Top]