| 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,KinnertonReluctant 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 usepk_<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. |
 |
|
|
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_TableNameFor Foreign Key : FK_<SourceTable>_<ChildTable>For Index : IX_<TableName>_<TableName> |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
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. |
|
|
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
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. |
|
|
|
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 indexCX_ -> 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 |
|
|
|
|
Renaming Indexes / naming conventions