Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
SQL Server Index Naming Guidelines
In terms of actual, concrete, guidelines for naming SQL Server Indexes, I only have two pieces of what I would consider perfect advice.
ITPro Today
April 25, 2013
5 Min Read
Alamy
In terms of actual, concrete, guidelines for naming SQL Server Indexes, I only have two pieces of what I would consider perfect advice:
Whatever you do, be consistent. You may have a great idea for a new index naming convention, but if you just ‘lump it in’ on top of other previous/failed naming schemes or conventions then you’re just further confusing things. Since the whole idea of a naming convention or scheme is attempt to impose order into our naturally chaotic universe, multiple flavors of order just end up being… chaotic – or less than optimal.
There’s no such thing as a perfect naming convention or naming scheme. I’ve been around SQL Server for over 13 years now, and in that time have seen some great contenders for perfect naming conventions, nomenclatures, and guidelines. But, invariably, there’s always some defect or problem with every single naming scheme (for tables, objects, code, indexes, what-have-you) that each of these solutions has bumped into. Sometimes it’s a question of sooner rather than later, but there is no ‘perfect system’ for anything. As such, any time you’re looking to potentially rename indexes across wide swaths of your infrastructure to make things perfect or ideal, then you’re not going to make it.
With those bits of solid advice out of the way, let me share some details about the approach that I currently use to name indexes – along with some candid details about where my own naming conventions are falling short.
Naming Conventions
In my mind, the primary purpose of any naming convention is to try and help impose some degree of order. To that end, I also like naming conventions to try and enable the ability to let named objects tell you a bit about the object, its purpose, and any particular (pertinent) details – at a glance. Stated differently, one of the reasons I name indexes as I currently do, is because it helps me get an ‘at a glance’ idea of core attributes of a given index.
To that end, I prefix all of my indexes with one of the following:
PK_ Primary Keys, which are logical constructs and not necessarily physical constructs, get tagged with a PK_ prefix. In most systems that I design the PK_ isn’t a clustered index. But, when it is, then I still prefix Clustered Primary Keys as PK_.
CLIX_ In my universe, every (non-PK) Clustered Index name is prefixed with CLIX (for CLustered IndeX). Personally, I typically don’t design too many explicitly Unique clustered indexes, but if I do I commonly struggle with whether to call them UCLIX_ or just CLIX_ or not. To me, UCLIX_ looks lame and doesn’t ‘sound cool’ – whereas CLIX has nice ‘aesthetics’. (Yes. I have major issues – but these are my guidelines and they’re what I try to impose order on my part of the universe.)
IX_ standard single or even multi-column indexes used for seeks or intersection just get prefixed with an IX_ (for IndeX). In cases where I need a composite index (i.e., multiple columns) but still explicitly ‘tolerate’ key/bookmark lookups, I’ll still call my seek/intersection/composite indexes IXes.
COVIX_ Whereas, on the other hand, any index that I design to specifically cover a query or set of operations entirely is called a COVIX_ (for COVering IndeX). Which, in turn, means that if I see a couple of these on a bigger table, I know it’s getting heavily queried by a number of different/distinct query patterns and specific COVIXes are being used to address specific needs/etc.
Only, while that aspect of my preferred naming scheme has worked well for a long time, it’s started to break-down (or run into friction) over the past few years as I’ve struggled with whether or not to try and indicate whether an index is filtered or not. Obviously, for CLIXes that’s not an issue. But with standards IXes and COVIXes, that’s a different matter. And, at that point, IF I try to indicate ‘filtered-ness’, then things (to me) start to get lame. For example, FIX_ is a fine prefix for a filtered index. But FCOVIX is dumb – and a reminder (to me) that no naming convention can ever be perfect.
On the other hand, I could leave the fact that an index is filtered OUT of the name, but that seems like I’m not doing myself any favors by making that obvious or visible ‘at a glance’. As such, I keep flip-flopping on how I handle this BETWEEN projects (i.e., I’m consistent – but only on a client-by-client or project by project basis).
Otherwise, the other component of my naming convention is that each index I create adheres to the following, overall, structure:
[Prefix]_TableName_[Description]
Where the [Prefix] is the info I just outlined above, and where the [Description] can actually vary a fair amount from one index to the next based upon WHY the index exists. Some examples will probably make that make more sense – as this is where my rules get a bit harder to ‘concretely’ define:
IX_Customers_ByEmail. This lets me know that the IX is simply to allow an optimized lookup against the Email column. And that this index is not intended for ‘covering’ – meaning that I’m explicitly intending either a lookup afterwards or using this IX in conjunction with others for intersection.
COVIX_Customers_HashedPassPhraseByEmail. Unlike the IX above, this lets me know that this index is a covering index that’s pulling in email and (hashed) password – or something that can be used to enable login (or something similar). In other words, the prefix and description ‘tell me everything’.
COVIX_OrderDetail_PriceDateItemCountShippedByHeaderId. Similar to the above, I have a good idea what’s going on here. Only, as you can guess, it’s quickly possible for descriptions to get so long and unwieldy that they offer ‘information overload’ instead of ‘at a glance’ info. For that, I have a secret weapon:
COVIX_Orders_ForHourlyCEOReport. Where the use of ‘For’ in the description tells me that I’ve explicitly created this index for (typically) a sproc or some OTHER regular query/problem that needs an explicit, covering, index to satisfy various things. Likewise, I’ll use the same approach with IXes – such as IX_Orders_ForFraudCheck and so on.
There’s also, of course, a ‘place’ where column-names and other descriptions vs naming indexes for specific operations happens to ‘break down’ or run into ‘friction’… at which point I just remember: nothing’s perfect, and try the best I can.
About the Author
You May Also Like
.jpg?width=700&auto=webp&quality=80&disable=upscale)
