Export (0) Print
Expand All
3 out of 6 rated this helpful - Rate this topic

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

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]

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]

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]

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.