SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Renaming Indexes / naming conventions
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kinnerton
Starting Member

21 Posts

Posted - 11/16/2012 :  11:13:00  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/16/2012 :  11:47:06  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/16/2012 :  15:21:18  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 11/16/2012 :  15:33:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 11/19/2012 :  04:26:31  Show Profile  Reply with Quote
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

Edited by - Kinnerton on 11/19/2012 05:47:32
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 11/19/2012 :  13:37:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 11/20/2012 :  04:17:23  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 11/20/2012 :  14:17:03  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 11/21/2012 :  05:59:59  Show Profile  Reply with Quote


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
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 11/26/2012 :  19:33:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000