It’s All in the Name, Index Naming Conventions

Awhile back, if you are on Twitter, you can probably recall my ranting about the 949 indexes I was reviewing. The process was to determine duplicate indexes and consolidate them or discard the unneeded ones. My ranting was not about the duplicates per se it was about the index names. It only takes a second to name an object with some name that tells what the thing is. Below I will show you some examples and give you an easy script that will help you generate your index names. Taking a little time to name things appropriately can go a long way, it can not only be time saving but can help to reduce redundancy.

The DONT’s

As you can see from above, none of the names gave a complete indication of what the index encompassed. The first one did indicate it was a Non Clustered Index so that was good, but includes the date which to me is not needed. At least I knew it was not a Clustered Index. The second index did note it is a “Covering Index” which gave some indication that many columns could be included I also know it was created with the Data Tuning Advisor due to the dta prefix. The third index was also created with dta but it was left with the default dta naming convention. Like the first one I know the date it was created but instead of the word Cover2, I know there are 16 key columns are noted by the “K#” and regular numbers tell me those are included columns. However, I still have no idea exactly what these numbers denote without looking deeper into the index. The last index is closer to what I like however the name only tells me one column name when in fact it encompasses five key columns and two included columns.

The DO’s

Above we see a few good examples with varying naming conventions, but each tell me a much more than what we saw in the “Donts” list. The first one I know right away is a non-clustered index with two fields. The second is a clustered index with one field. The third is an index that has 9 fields, probably a covering index of some sort, which tells me that it is probably important to a specific query or procedure. Index four uses the name of the table and the field, which does give me more information but given the name of indexes are limited to 128 characters I prefer to leave that out. The last one closer to one of my favorites, because it does give more information. The name lets us know that it has an included column of Birthdate.

The Script

Here is the script I use when creating indexes. It will go thru and identify a missing index and create Index statement using a standard name convention.
NOTE: This modified version of what we use at DCAC is for just showing you how I include and create a standard statement in my code, this is not to be used to identify missing indexes, as it is not the purpose of my post. I have removed pieces of that from this script.

Create Statement Output

This statement gives the proper database context and create statement syntax, it adds all the needed key columns within the () and separated by commas. In addition, it adds the word INCLUDE and encompasses the included columns also in () and comma separated. Note the index name only includes the Key columns, which is just my preference.

Summary

Now everyone has their own naming conventions. You do you, however should stay consistent and give some meaning to it. When others look at the objects we should be able to know what it’s doing or be given a good clue as to what it’s for. This not only helps to quickly identity its definition but also keep you from creating duplicates. By looking at names you can tell with columns you need are already included in other indexes. Naturally you can’t just trust the name you have to dig deeper while examining your indexes but it at least will give you a realistic starting point.

So let’s talk naming conventions

How many of you have come across a database that had stored procedures, views or functions and you had no clue, by name, what they were for? Having standard naming conventions helps to prevent that. Everyone has their own preferences and opinions on what they should be, so I thought I’d share mine.

My opinion

In a nutshell, the name of any object should be informative; specifically what the object is used for and where it is used. This is accomplished by utilizing prefixes in conjunction with specific naming conventions.  I apply these standards to all of my stored procedures, views, and functions, whenever possible. Of course there are always times where naming conventions don’t fit the situation, you have to be flexible and logically choose a name that fits that scenario.

Here Are My Standards

standards

Stored Procedures

  • Stored Procedure name should accurately reflect the content and function
  • No Spaces
  • No Underscores
  • Use Camel Case Only example “rptTheNewStoredProcedure”
  • The following prefixes should be used for all names
  • New Prefixes can be added as needed when a deemed applicable
Prefix Use
rpt Reporting Services Report
app Applications
web Website Exclusively
xls Direct Excel Data Pull
ssis Store Procedure only used in SSIS packages
job SQL Agent Job Step
cube Analysis Services Cube Data

If a stored procedure has multiple uses, I choose the highest entry in the usage hierarchy as the prefix. For example, a job that calls a stored procedure for an application. The job is what calls it, so it would be named with the job prefix. Using the prefix job will point me to SQL Agent. There I should see a job having a job step with same name as stored procedure minus the prefix and a stored procedure of the same name prefixed with job. At any point in it should give the DBA a trail to follow. Don’t duplicate a stored procedure to use in multiple places. Otherwise, any change will have to be done multiple times.

Views

  • View name should accurately reflect the content and function
  • No Spaces
  • No Underscores
  • Use Camel Case only example “rptVwTheNewView”
Prefix Use
rptVw Reporting Services Reports
appVw Applications
webVw Website Exclusively
jobVw Job Related
cubeVw Analysis Services Cube

I add Vw just so I can differentiate it from a stored procedure or table. Now you may question why I add Vw to views but not p or proc prefix to stored procedures. It’s simply because for me stored procedures are the default and doesn’t need it.

Functions

  • Function name should accurately reflect the content and function
  • No Spaces
  • No Underscores
  • Prefix with fx
  • Use Camel Case only example “fxTheNewFunction”

Because functions can be called by lots of objects I tend only to use fx and not add rpt etc. Functions are the only objects that deviate from the norm for me.

Right Way

The best use example I can give for how I try to implement naming conventions as a way to identify its use is a stored procedure for a reporting services report. The stored procedure should be named exactly what the report is or will be named, but will have the prefix of rpt. Whoever comes into the database should look at the name and know it’s;

  1. A stored procedure called by a report
  2. There will be an .rdl file that has the exact same name minus the prefix.

Exhibit A

Stored Procedure: rptCostofManufacturedItems

Report Services Report: CostofManufacturedItems.rdl

The Not So Right Way

wrong

The not so correct way

Like the toilet paper roll being put on the wrong way. It is a huge pet peeve of mine to see stored procedures named something that you cannot tell what it is. I get irritated when people prefix a stored procedure it p_ or proc, it’s just a thing for me.  I also despise the wretched underscore and mismatched casing. As you can probably tell, I am particularly persnickety when it comes to naming conventions.

Exhibit B (a real exhibit I have encountered)

Stored Procedure: p_DatabaseA_Skus_customers_ROLLING_vj_v5

Report Services Report: customer items.rdl
The name, as I figured out, was “p” for procedure, database name (which makes no sense since I can find it under that database), then procedure use, the developers initials, and finally a version number.  As you can see the report that is it associated with, tells you in absolutely no way shape or form, that it uses that procedure. Notice the “v5” in the name.  Yep, there were 5 of these puppies.  I could not tell without locating the report which one was actually used. Finding which report was a task in and of its self since the report was named completely different from the stored procedure. With naming conventions like this you waste time just trying to figure out what goes with what.

What’s your standard?

Everyone’s environment is different and some may not agree with my opinion, but it works for me. I’d be interested to hear other standards people use. Maybe I can pick up an idea or two.

The key to this is to come up with standards of your own and implement them.  Use them not only for the objects I listed but also for  triggers, indexes, jobs, and replication. It’s less important WHAT your conventions are. It’s more important that you have them. Pick what works for your company’s business model and push all those who deploy to production, including report writers, to use the same naming conventions.  Cleanly named objects make for happy DBA’s.