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.

   1: DROP INDEX ix_IndexName

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:

   1: -- paste in create index statement

   2: CREATE NONCLUSTERED INDEX ix_IndexName

   3: ON Sales.SalesOrderHeader ( [TerritoryID],[ShipMethodID], [SubTotal], [Freight] )

   4: INCLUDE ([SalesOrderNumber], [CustomerID]);

As a test, I then added this index:

   1: CREATE NONCLUSTERED INDEX ix_IndexName

   2: ON Production.Product ( [Name],[ListPrice]);

Same name, different table.

A quick check in sys.objects surprised me.

   1: select *

   2:  from sys.objects

   3:  where name = 'ix_Indexname'

This returned no results. Hmmm, let’s investigate further. I next decided to check sys.indexes.

   1: select *

   2:  from sys.indexes where name = 'ix_Indexname'

This returned two results:

Two entries, with two object_ids. I wondered what those objects were, so I ran more code:

   1: select *

   2:   from sys.objects

   3:   where object_id in (1010102639, 1717581157)

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:

   1: -- cleanup

   2: DROP INDEX ix_IndexName

   3:  ON Sales.SalesOrderHeader

   4: ;

   5: GO

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.

   1: Drop INDEX ix_IndexName

   2: ON Production.Product

   3: ;

Filed under: Blog Tagged: administration, indexing, sql server, syndicated