MS SQL Server 2012 - DBA Articles

Drop Index

The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. To delete the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.
The following example describes to delete IX_Employee_Nonclustered index on Employee table.

-- Example 166 --

USE EBusiness

DROP INDEX IX_Employee_Nonclustered

ON dbo.Employee

Query Output Screen

Limitations and Restrictions
01. When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files. When a clustered index is dropped, the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in the resulting unordered table, a heap. All the space previously occupied by the index is regained. This space can then be used for any database object.
02. An index cannot be dropped if the filegroup in which it is located is offline or set to read-only.
03. When the clustered index of an indexed view is dropped, all nonclustered indexes and auto-created statistics on the same view are automatically dropped. Manually created statistics are not dropped.
04. The syntaxtable_or_view_name.index_name is maintained for backward compatibility. An XML index or spatial index cannot be dropped by using the backward compatible syntax.
05. When indexes with 128 extents or more are dropped, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.
Sometimes indexes are dropped and re-created to reorganize or rebuild the index, such as to apply a new fill factor value or to reorganize data after a bulk load. To do this, using ALTER INDEX is more efficient, especially for clustered indexes. ALTER INDEX REBUILD has optimizations to prevent the overhead of rebuilding the nonclustered indexes.

* * * * *

Your Comment To AUTHOR