Deleting an Index
When you no longer need an index, you can delete it from a database and reclaim the storage space it currently uses. This reclaimed space can then be used by any object in the database.
Deleting a clustered index can take some time, because all nonclustered indexes on the same table must be rebuilt. For more information about the relationship between clustered and nonclustered indexes, see Nonclustered Indexes.
You cannot delete an index used by either a PRIMARY KEY or UNIQUE constraint without deleting the constraint. To delete and re-create an index used by a PRIMARY KEY or UNIQUE constraint without having to delete and re-create the constraint (for example, to reimplement the original fill factor used by the index), rebuild the index in one step. For more information about rebuilding the index, see Rebuilding an Index. An index specified as the full-text key for the table cannot be deleted. View index properties to determine if the index is the full-text key.
Rebuilding an index, rather than deleting and re-creating it, is also useful to re-create a clustered index, because the process of rebuilding the index can remove the need to sort the data by the index columns if the data is already in sorted order.
Indexes created on any views or tables (permanent and temporary) are automatically deleted when the view or table is deleted.
Note Only the owner of a table can delete its indexes. The owner cannot transfer the permission to other users.
To delete an index
Transact-SQLTo view index properties