Dropping Indexes
When you no longer need an index, you can remove it from a database and reclaim the disk space it currently uses. Any object in the database can then use this reclaimed space. Deleting an index and dropping an index are synonymous.
You cannot drop an index used by either a PRIMARY KEY or UNIQUE constraint, except by dropping the constraint. By modifying the index, for example, to modify the fill factor value used by the index, you can essentially drop and re-create an index used by a PRIMARY KEY or UNIQUE constraint without having to drop and re-create the constraint. For more information about rebuilding the index, see Reorganizing and Rebuilding Indexes.
Rebuilding an index, instead of dropping and re-creating it, is also useful to re-create a clustered index. This is because the process of rebuilding the index can remove the requirement to sort the data by the index columns if the data is already in sorted order.
Indexes created on any views or tables, both permanent and temporary, are automatically dropped when the view or table is dropped.
Note
You can drop an index if you have ALTER permissions on the table.
Clustered Indexes
When a clustered index is dropped, the data rows that were stored in the leaf level of the clustered index are stored in an unordered table (heap). Dropping a clustered index can take time because in addition to dropping the clustered index, all nonclustered indexes on the table must be rebuilt to replace the clustered index keys with row pointers to the heap. When you drop all indexes on a table, drop the nonclustered indexes first and the clustered index last. That way, no indexes have to be rebuilt. For more information about the relationship between clustered and nonclustered indexes, see Nonclustered Index Structures.
Temporary disk space is required to drop a clustered index during an online drop operation, or when the MOVE TO clause is specified. For more information, see Disk Space Requirements for Index DDL Operations.
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.
Using the MOVE TO Clause
In SQL Server 2005, you can drop the clustered index and move the resulting unordered table (heap) to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option. The MOVE TO option has the following restrictions:
- It is not valid for indexed views or nonclustered indexes.
- The specified partition scheme or filegroup must already exist.
- If MOVE TO is not specified, the resulting table will be located in the same partition scheme or filegroup as was defined for the clustered index.
Dropping Clustered Indexes Online
You can specify the ONLINE option when you drop a clustered index. When set to ON, queries and modifications to the underlying data and associated nonclustered indexes are not blocked by the DROP INDEX transaction. For more information, see Performing Index Operations Online.
When set to ON, the ONLINE option has the following restrictions:
- Only one index can be dropped at a time.
- It is not valid for disabled clustered indexes.
- It is not valid for a clustered index on a view, or nonclustered indexes on tables or views.
- A clustered index that contains text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or xml columns in the leaf level data rows cannot be dropped online.
Setting the MAXDOP Index Option
In SQL Server 2005, you can override the max degree of parallelism configuration option of sp_configure for the drop index operation by specifying the MAXDOP index option. For more information, see Configuring Parallel Index Operations.
Full-Text Indexes
An index specified as the full-text key for the table cannot be dropped. View index properties to determine whether the index is a full-text key. For more information, see INDEXPROPERTY (Transact-SQL).
To drop an index
How to: Delete an Index (SQL Server Management Studio)
Examples
A. Dropping an index
The following example drops the index IX_ProductVendor_VendorID in the ProductVendor table.
USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor;
GO
B. Dropping a clustered index in the ONLINE mode
The following example drops a clustered index with the ONLINE option set to ON. The resulting unordered table (heap) is stored in the same filegroup as the index was stored.
USE AdventureWorks;
GO
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO