DROP INDEX SYNTAX

Here is the syntax needed in order to drop a single index:

[cc lang=”sql”]
USE YourDatabaseName;
DROP INDEX IX_Product_1
ON dbo.Product;
[/cc]

You can also drop multiple indexes within a single transaction:

[cc lang=”sql”]
USE YourDatabaseName;
DROP INDEX
IX_Product_1 ON dbo.Product,
IX_Customer_1 ON dbo.Customer;
[/cc]

Permissions

The permissions needed to drop an index are alter permissions to the table. This is inherent in dbo, sysadmin, and ddl_admin fixed server roles.

Locking / Blocking

When dropping an index, a schema manipulation lock is acquired for the duration of the delete which is not compatible with any other type of lock so this operation will cause blocking. This is required in order to notify all dependent execution plans they need to recompile. Even using an online index hint will not stop the schema manipulation lock. The only time the online index hint will help is when dropping the clustered index (moving it to a heap).