SQL Server Error Messages - Msg 159¶

Error Message¶

-- SQL Server 2005, SQL Server 2008
Server: Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.

-- SQL Server 2000
Server: Msg 159, Level 15, State 1, Line 1
For DROP INDEX, you must give both the table and the index name, in the form tablename.indexname.

Causes¶

An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.

The basic syntax for creating an index is as follows:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX <Index Name>
ON <Object> ( <Column Name> [, …n] )

Unlike table names and view names which must be unique within a database, index names need not be unique within the database, but must be unique within a table or view. The same index name can be used in different tables or views. To illustrate:

CREATE TABLE [dbo].[Student] (
    [StudentID]    INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [FirstName]    VARCHAR(50),
    [LastName]     VARCHAR(50)
)

CREATE TABLE [dbo].[StudentClass] (
    [StudentClassID]    INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [StudentID]         INT,
    [ClassID]           INT
)

CREATE TABLE [dbo].[StudentClassGrade] (
    [StudentClassGradeID]   INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [StudentID]               INT,
    [ClassID]                 INT,
    [Grade]                   INT
)

CREATE INDEX [IX_StudentName] ON [dbo].[Student] ( [LastName], [FirstName] )

CREATE INDEX [IX_StudentID] ON [dbo].[StudentClass] ( [StudentID] )

CREATE INDEX [IX_StudentID] ON [dbo].[StudentClassGrade] ( [StudentID] )

Since the same index name can be used in different tables or views, in SQL Server 2005 and SQL Server 2008 dropping an index with just the index name will generate the following error.

DROP INDEX [IX_StudentID]

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

In SQL Server 2000, the same DROP INDEX command will generate the same error number but with a slightly different error message:

Server: Msg 159, Level 15, State 1, Line 1
For DROP INDEX, you must give both the table and the index name, in the form tablename.indexname.

This error also occurs even if the name of the index being dropped is unique. Dropping the [IX_StudentName] index above just using the index name will also generate this error:

DROP INDEX [IX_StudentName]

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

Solution / Work Around¶

In SQL Server 2005 and SQL Server 2008, there are 2 ways of dropping an index. The first method, which is the recommended syntax, is as follows:

DROP INDEX <Index Name> ON <Object Name>

Using this method of dropping an index, this is how to drop the IX_StudentID index of the [dbo].[StudentClass] table:

DROP INDEX [IX_StudentID] ON [dbo].[StudentClass]

The second way of dropping an index is by qualifying the index name with the object name, as follows:

DROP INDEX <Object Name>.<Index Name>

This method of dropping an index is the only method available in SQL Server 2000. This syntax of dropping an index will be removed in a future version of SQL Server and is included in SQL Server 2005 and SQL Server 2008 just for backward compatibility. Avoid using this syntax in new development work, and plan to modify applications that currently use syntax.

Using this method of dropping an index, this is how to drop the IX_StudentName index of the [dbo].[Student] table:

DROP INDEX [dbo].[StudentClassGrade].[IX_StudentID]