SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Dropping Indexes

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


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

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

As a test, I then added this index:


   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

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.wordpress.com, opens in a new window]

Loading comments...