What are the advantages of include table name in index name?

Question

I am going to rename all my database index names and are looking for a good convention to do this.

Lots of people suggest to include the name of the table in the index name.

What are the advantages of doing this?

I can't see many uses for this. On the contrary it bloats the name.

I like IX_MyIndexName more than IX_MyVeryLongTableName_MyIndexName

Answer 1

This common convention is very useful when you need to identify what table an index belongs to. This can be very useful if you have any reporting/monitoring/alerting system which show indexes that require rebuilding or indexes that are taking too much space - you'll be able to easily identify which table the index belongs to without having to perform additional queries and make an informed decision on how urgent the situation is, what parts of the application it effects, etc.

Answer 2

Having table name inside will guarantee you don't hit duplicate index name in the future.

I would not care much of index name until it fits in the max possible length.

IDX_{TABLE_NAME}_{INDEX_NAME}

I would suggest whatever you plan to use be consistent everywhere on 100%, this will solve you some troubles in the future.