Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Renaming Indexes / naming conventions

Author  Topic 

Kinnerton
Starting Member

21 Posts

Posted - 2012-11-16 : 11:13:00
Hi,

Does renaming indexes have any impact on the index themselves? Will it invalidate any cached execution plans - or is the name purely cosmetic and it will still retain an embedded id?

I've been given the task of sorting out the indexes on our production db and first look shows a dozen of random (or no) naming conventions have been used making them hard to rationalise.

My plan is to rename them to IX_[Table Name]_[Index fields] (or _PK_[Table Name]_[Index fields] for clustered indexes) - or is there a better approach?

Cheers,

Kinnerton
Reluctant DBA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-11-16 : 11:47:06
Name doesn't matter unless someone has added index hints.

PK is usually used for primary keys rather than clustered indexes - but maybe that's a better use.

I usually use
pk_<table>
ix_<table>_nn where nn is a sequence number



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-16 : 15:21:18
It won't impact.

We use:

For Primary Key : PK_TableName
For Foreign Key : FK_<SourceTable>_<ChildTable>
For Index : IX_<TableName>_<TableName>
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-11-16 : 15:33:27
We include the column names in the index names as it saves me time in production when I'm performance tuning. The sequence number approach means I have to check each index to see what's in it. While my index names can be long, who cares?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kinnerton
Starting Member

21 Posts

Posted - 2012-11-19 : 04:26:31
Many thanks for all the responses.

Glad to see I'm not too far off base!

The one issue I'm finding are indexes whose table name and indexed fields are the same - but the included fields differ. I've got a feeling this may be a 'it depends' question - Is it good practice to merge these into one index that contains all of the included fields and drop the rest?

Cheers,

Kinnerton
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-11-19 : 13:37:27
I would merge them into one index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kinnerton
Starting Member

21 Posts

Posted - 2012-11-20 : 04:17:23
The fact that you are answering noob questions like this after 34,000 posts is not only aspirational and humbling but is starting to make your Goddess status seem inadequate. Surely 'legendary' should be in there somewhere.

Thanks Tara.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-11-20 : 14:17:03
quote:
Originally posted by Kinnerton

The fact that you are answering noob questions like this after 34,000 posts is not only aspirational and humbling but is starting to make your Goddess status seem inadequate. Surely 'legendary' should be in there somewhere.

Thanks Tara.





You're welcome, glad to help. I like helping people, regardless of the type of question. And I wouldn't consider this a newbie topic anyway.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Kinnerton
Starting Member

21 Posts

Posted - 2012-11-21 : 05:59:59


I've just applied a single index rename to our production box and it invalidated around a dozen stored proc cached execution plans.

After I recompiled them (and some shouty users in the interim), they were back to their speedy selves.
Go to Top of Page

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-11-26 : 19:33:02
We use a couple of other prefixes for our indexes:
UQ_ -> Unique index
CX_ -> Clustered index (There is some internal debate about using this)

=================================================
We are far more concerned about the desecration of the flag than we are about the desecration of our land. -Wendell Berry
Go to Top of Page
   

- Advertisement -