Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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

   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:

indexes

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.

indexes2

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

Comments

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

Loading comments...