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?
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.
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?
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?
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.
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.
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
Renaming Indexes / naming conventions
Posted - 11/16/2012 : 11:13:00
