While working on some demos recently, I needed to drop an index for a test. I executed this generic statement for an index I’d just created.
Needless to say I was surprised when I got this error:
Msg 159, Level 15, State 1, Line 1
Must specify the table name and index name for the DROP INDEX statement.
I haven’t done much index maintenance in the last few years, but since I had specified the name, and I expected names to be unique, I was surprised. That’s not the case, however, since indexes aren’t seen as objects.
I created the index in AdventureWorks with this code:
As a test, I then added this index:
Same name, different table.
A quick check in sys.objects surprised me.
This returned no results. Hmmm, let’s investigate further. I next decided to check sys.indexes.
This returned two results:
Two entries, with two object_ids. I wondered what those objects were, so I ran more code:
I received the two tables back as the objects.
This surprised me, though I’m sure I’ve read the details in a book at some point, or even seen the documentation in sys.indexes. The entry for name says it is unique only within the space of the object, which would be the parent table.
I had assumed that indexes were objects, but they aren’t. They are an attribute of an object, and as such, I needed this code to remove my index:
Update: As noted in a few comments, you can also drop the index as:
2: DROP INDEX Sales.SalesOrderHeader.ix_IndexName
And, of course, I needed to drop my test object.