Delete an Index
This topic describes how to delete (drop) an index in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To delete an index, using:
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.
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.
In Object Explorer, expand the database that contains the table on which you want to delete an index.
Expand the Tables folder.
Expand the table that contains the index you want to delete.
Expand the Indexes folder.
Right-click the index you want to delete and select Delete.
In the Delete Object dialog box, verify that the correct index is in the Object to be deleted grid and click OK.
In Object Explorer, expand the database that contains the table on which you want to delete an index.
Expand the Tables folder.
Right-click the table that contains the index you want to delete and click Design.
On the Table Designer menu, click Indexes/Keys.
In the Indexes/Keys dialog box, select the index you want to delete.
Click Delete.
Click Close.
On the File menu, select Savetable_name.
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
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).