SQL Server - DROP INDEX Keyword

The SQL Server (Transact-SQL) DROP INDEX keyword is used to delete a index on a table. Indexes are useful to retrieve data from the database more quickly and speeds up the search queries. Indexes are not visible to the end users. Along with this, updating a table with indexes takes more time as compared to a table without any indexes because indexes are updated. Therefore it is advised to create indexes only on frequently searched columns.

The INSERT and UPDATE statements take more time on tables having indexes, whereas the SELECT statements become fast on those tables. The reason is that while performing insert or update operation, the database update the indexes as well.

SQL Server DROP INDEX

The SQL Server DROP INDEX statement is used to delete a index on a table.

Syntax

The syntax for using DROP INDEX statement in SQL Server (Transact-SQL) is given below:

/* Method 1 */
DROP INDEX index_name ON table_name;

/* Method 2 */
ALTER TABLE table_name
DROP INDEX index_name;

/* Method 3 */
DROP INDEX table_name.index_name;

/* Method 4 */
DROP INDEX index_name;

SQL Server DROP INDEX Example

Consider a table called Employee which contains an index named idx_name. To drop this index, the following statement can be used:

/* Method 1 */
DROP INDEX idx_name ON Employee;

/* Method 2 */
ALTER TABLE Employee
DROP INDEX idx_name;

/* Method 3 */
DROP INDEX Employee.idx_name;

/* Method 4 */
DROP INDEX idx_name;