DROP INDEX
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" in this volume.
Syntax
DROP INDEX 'table.index | view.index' [ ,...n ]
Arguments
table | view
Is the table or indexed view in which the indexed column is located. To see a list of indexes that exist on a table or view, use sp_helpindex and specify the table or view name. Table and view names must conform to the rules for identifiers. For more information, see Using Identifiers. Specifying the table or view 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" in this volume.
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.
Similarly, when the clustered index of an indexed view is dropped, all nonclustered indexes on the same view are dropped automatically.
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 are not transferable. However, members of the db_owner and db_ddladmin 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