Delete an index
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This topic describes how to delete (drop) an index in SQL Server by using SQL Server Management Studio or Transact-SQL.
In This Topic
Before you begin:
To delete an index, using:
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.
Using SQL Server Management Studio
To delete an index by using Object Explorer
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.
To delete an index using Table Designer
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.
Using Transact-SQL
To delete an index
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 AdventureWorks2022; 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).