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:

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.

Related: Indexing Dos and Don'ts

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:

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:

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. 

Related: VBToolkit: Naming Conventions and Structured Programming