DROP INDEX (Transact-SQL) - SQL Server Documentation

Syntax


DROP INDEX { index_name ON table_or_view_name }
[ WITH ,index_option [ ,...n ] ]

DROP INDEX { index_name [ ,...n ] }
ON table_or_view_name
[ WITH ,index_option [ ,...n ] ]

DROP INDEX index_name
ON schema_name.table_or_view_name
[ WITH ,index_option [ ,...n ] ]

DROP INDEX index_name [ ,...n ]
ON schema_name.table_or_view_name
[ WITH ,index_option [ ,...n ] ]

-- Obsolete syntax for dropping indexes
DROP INDEX schema_name.index_name
ON schema_name.table_or_view_name

DROP INDEX schema_name.index_name
[ WITH ,index_option [ ,...n ] ]

DROP INDEX index_name
ON schema_name.table_or_view_name
[ WITH ,index_option [ ,...n ] ]

Arguments

index_name

The name of the index to drop. If the index is a heap, then the index name must be the default heap index name, which is the clustered index key name if one exists, or the default name given by SQL Server when the heap was created.

table_or_view_name

The name of the table or view on which the index is created. If the index exists on a table or view that has a different schema name than the one specified in the ON clause, then table_or_view_name must be qualified with the correct schema name.

schema_name

The name of the schema to which the table or view belongs. If omitted, the default schema of the current user is used.

index_option

Specifies whether to drop the index online or offline. Available options are:

(DROP_EXISTING = ON | OFF)
(ONLINE = ON | OFF)
(MAXDOP = integer)
(WAIT_AT_LOW_PRIORITY ( MAX_DURATION = wait_duration , ABORT_AFTER_LOW_PRIORITY ) )

Description

DROP INDEX can be used to remove one or more indexes from a table or view. Dropping an index removes the index definition and the index data pages.

The syntax of DROP INDEX has evolved over different versions of SQL Server. It's recommended to use the most current syntax.

Note

If you drop a clustered index, all nonclustered indexes on the table are also dropped because nonclustered indexes contain pointers to the clustered index key.

Tip

You can use the sys.indexes catalog view to find the names of all indexes on a table or view.

Permissions

By default, permissions for DROP INDEX are granted to members of the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles. The permissions required for DROP INDEX are the same as the permissions required to create an index.

Examples

Example 1: Dropping a nonclustered index


DROP INDEX IX_EmployeeContact
ON HumanResources.vEmployee;

Example 2: Dropping multiple nonclustered indexes


DROP INDEX IX_ProductVendor_Vendor_Product
ON Purchasing.ProductVendor;

DROP INDEX IX_ProductVendor_Product_Vendor
ON Purchasing.ProductVendor;

Example 3: Dropping a clustered index


DROP INDEX PK_Product_ProductID
ON Production.Product;

Example 4: Dropping an index online


DROP INDEX IX_CustomerOrders_OrderDate
ON Sales.SalesOrderHeader
WITH ( ONLINE = ON );

Warning

Online index operations require the Enterprise Edition of SQL Server.