Dropping Indexes

When you no longer need an index, you can remove it from a database and reclaim its storage space by dropping it. For example, consider dropping an index if you do not use it for most or all of your queries.

Note Only the owner of the index can drop it. The owner cannot transfer drop permission to other users.

    To drop an index

• In SQL Enterprise Manager's Server Manager window, select the appropriate table, click the right mouse button, choose Indexes, select the index from the Index drop-down list, and then choose the Remove button.

  (For details, search for "indexes" in SQL Enterprise Manager Help.)

Or

• Use the DROP INDEX statement.

  For example:

  DROP INDEX authors.au_id_ind

  drops the au_id_ind index from the authors table.

  (For details, see the DROP INDEX statement in the Microsoft SQL Server Transact-SQL Reference.)