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:

  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.

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 Savetable_name.

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 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).