DROP INDEX - Must specify the table name and index name for the DROP INDEX statement

In this post i would like to show you, how to drop an index in SQL Server.

If you came from different database backgrounds like Oracle or DB2, then you will issue the following statement and expect to execute without errors.

DROP INDEX INDEX_NAME;

This will not work in SQL Server. You need to specify the on which table this index is defined.
When you execute the above statement you will get below error:

Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.

The right way to drop an index is:

DROP INDEX INDEX_NAME ON TABLE_NAME;

Related Articles:
Clustered Index in SQL Server
Non Clustered Index in SQL Server
Covering Index in SQL Server
Filtered indexes in SQL Server 2008

Reference: Vijaya Kadiyala (www.DotNetVJ.com)