DROP INDEX (T-SQL)

Removes one or more indexes from the current database.

The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints (created by using the PRIMARY KEY or UNIQUE options of either the CREATE TABLE or ALTER TABLE statements, respectively). For more information about PRIMARY or UNIQUE KEY constraints, see CREATE TABLE or ALTER TABLE.

Syntax

DROP INDEX 'table.index' [,...n]

Arguments

table: Is the table in which the indexed column is located. To see a list of indexes that exist on a table, use sp_helpindex and specify the table name. Table names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the table owner name is optional.
index: Is the name of the index to be dropped. Index names must conform to the rules for identifiers.
n: Is a placeholder indicating that multiple indexes can be specified.

Remarks

After DROP INDEX is executed, all the space previously occupied by the index is regained. This space can then be used for any database object.

DROP INDEX cannot be specified on an index on a system table.

To drop the indexes created to implement PRIMARY KEY or UNIQUE constraints, the constraint must be dropped. For more information about dropping constraints, see ALTER TABLE.

Nonclustered indexes have different pointers to data rows depending on whether or not a clustered index is defined for the table. If there is a clustered index the leaf rows of the nonclustered indexes use the clustered index keys to point to the data rows. If the table is a heap, the leaf rows of nonclustered indexes use row pointers. If you drop a clustered index on a table with nonclustered indexes, all the nonclustered indexes are rebuilt to replace the clustered index keys with row pointers.

Sometimes indexes are dropped and re-created to reorganize the index, for example to apply a new fillfactor or to reorganize data after a bulk load. It is more efficient to use CREATE INDEX and the WITH DROP_EXISTING clause for this, especially for clustered indexes. Dropping a clustered index causes all the nonclustered indexes to be rebuilt. If the clustered index is then re-created, the nonclustered indexes are rebuilt once again to replace the row pointers with clustered index keys. The WITH DROP_EXISTING clause of CREATE INDEX has optimizations to prevent this overhead of rebuilding the nonclustered indexes twice. DBCC DBREINDEX can also be used and has the advantage that it does not require that the structure of the index be known.

Permissions

DROP INDEX permissions default to the table owner, and is not transferable. However, members of the db_owner and dll_admin fixed database role or sysadmin fixed server role can drop any object by specifying the owner in DROP INDEX.

Examples

This example removes the index named au_id_ind in the authors table.

USE pubs

IF EXISTS (SELECT name FROM sysindexes

WHERE name = 'au_id_ind')

DROP INDEX authors.au_id_ind

GO