What is the SQL index name for?

Question

In SQL, when I create a database index, I'm required to give the index a name, as in

CREATE INDEX timestamp_index ON hit (timestamp);

The only time that this name seems to be necessary is if I want to remove the index with

DROP INDEX timestamp_index;

Is there any other use of the index name?

Answer 1

How else would you identify the index?

There is probably logging attached as well.

Answer 2

Sometimes it's necessary to do index hinting, whereby you tell the query engine what index it should be using. If your indexes don't have named, you can't do this.

Answer 3

ALTER INDEX timestamp_index ...

T-SQL link

Answer 4

It's also helpful when viewing/reviewing execution plans (at least in SQL Server), since the indexes used are shown by name.

Answer 5

Here are a couple of things to think about:

How would you rebuild the index?

How would you defragment/reorganize the index?

how would you alter the index?

How would you look at the index to see what columns are in it?

When viewing an execution plan how would you know what index was used?

How would you disable the index when doing BULK INSERT/BCP?

How would you use the sys.dm_db_missing_index_details DMV when you don't know the name?

BTW when you create a primary key a clustered index will be created by default, SQL Server will give it a name for you

create table bla  (id int primary key)
go

select * from sys.sysobjects s 
join sys.sysobjects s2 on s.parent_obj = s2.id
where s2.name = 'bla'

Here is the name that got generated for that index PK__bla__3213E83F66603565

Answer 6

I often feel the same way about filenames. Does an mp3 filename need to contain metadata (artist, album, is not a podcast, etc) when the file's attributes does a better job? The files on my ipod do not resemble the corresponding filenames on my hard drive because it uses an index and I wasn't required to think up a name for that index!

Unlike a data element name, the name of an index is arbitrary. The ideal index is one that gets created and forever more 'looked after' by the system as the system knows best. Sadly, the reality is that on the rare occassions you may need to do something with it (e.g. drop it!). You could refer to a system dictionary to identify the index based on its attributes, usually just the columns it comprises.