THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at sqlblog.com since 2006, focusing on manageability, performance, and new features, and also blogs at blogs.sentryone.com and SQLPerformance.com; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Bad habits to kick : inconsistent naming conventions

In my last post in this series, I talked about using the schema prefix, with particular focus on dbo-only systems.  In this post, I want to treat the use of inconsistent naming conventions.

Stored Procedures

In one of the systems I've inherited, we have stored procedures written by different people (or even by the same people at different phases of development) that defy any sensible naming convention.  Imagine stored procedures revolving around a Customers table, and having stored procedures named like this:

dbo.AddCustomer
dbo.Customer_Create
dbo.Customer_GetList
dbo.Customer_GetDetails
dbo.GetCustomer
dbo.CustomerUpdate
dbo.GetCustomerList

When I'm maintaining this database, I sometimes feel like I'm trading hockey baseball cards with the original and no-longer-present developers: "got it, got it, need it, got it."  Obviously you can see that there are some duplicates there, and procedures were probably developed by one developer who couldn't find the one written by the previous developer, because it didn't sort the same way. 

Personally, my preference is {Entity}_{Verb}.  Why?  Because then all the procedures revolving around Customers are in very close proximity when sorting the list of procedures, and then the action words sort the same way within each entity.  I am seldom trying to find "stored procedures that *get* something" and then want to find the one named GetCustomerList.  I am much more apt to find it if I focus on the Customer-based procedures, then find the GetList procedure.  This is particularly true if the system has a *lot* of entities, and therefore a *lot* of stored procedures that all start with "Get."

But is my preference right for you?  Maybe not.  As has been mentioned several times in this series, what convention you choose is not quite as important as how consistent you are in using it.  The convention should have some kind of logic and reasoning behind it, but everyone working on the system should be very aware what the convention should be *before* they start creating objects.

Tables

Tables fall under the same scrutiny in my mind.  I hate when I see tables named dbo.Customers and then dbo.Employee and then dbo.tblOrders.  Tables should either be plural or not, and prefixed or not; within the same system, I should not see a mixture.  --CELKO-- has stated many times in the past that Employee is bad, Employees is better, but Personnel is best.  But that's for you to decide.  I'm not even going to tell you my preference, because it doesn't matter.  Pick what works for you, but STICK WITH IT and ENFORCE IT.

Columns / Parameters

If you use the same type of column or parameter in several objects or procedures, be consistent.  For example, you may have a DATETIME or SMALLDATETIME column for registering the date and time a row was inserted into the table.  Don't call it create_date in one table, CreatedDate in another, and DateCreated in a third.  This can prove very maddening when writing queries against tables, even if you have IntelliSense enabled.  If you aren't the one responsible for the naming convention, or if a conventions document doesn't exist, check other tables first, to quickly discover the existing convention.  If you find multiple conventions, talk to someone about it (you may be spotting a legacy table that can't be changed to match the current / reigning convention).  Barring that, go with the name of the relevant column on the newest table.

The same holds true for parameters to stored procedures.  I have seen reporting systems where the date inputs were named @StartDate/@EndDate, @date_start/@date_end, @RangeStart/@RangeEnd, etc.  I've also seen inconsistent ordering of the parameters, which isn't really a naming convention issue, but it can be a real problem if you are running procedure calls ad hoc and one expects @StartDate then @EndDate, and another expects @date_end then @date_start.  I am not sure what drives people to order such parameters in the latter fashion, but I've seen it, so thought I would mention it.

Summary

Naming conventions are a very subjective thing, but that doesn't mean you shouldn't pay attention to it.  For a long-lasting and easy-to-maintain system, you should come up with a convention and dedicate yourself to using it.  (For some examples, or if you don't know where to start, you can see these articles to get your mind working.)

I am working on a series of "Bad habits to kick" articles, in an effort to motivate people to drop some of the things that I hate to see when I inherit code.  Up next: making assumptions about IDENTITY.

Published Sunday, October 11, 2009 2:06 PM by AaronBertrand

Comments

 

Nigel Ainscoe said:

And with SQL 2008 the use of Policies to enforce some of this is now available which could be a good thing if used sensibly.

October 11, 2009 2:06 PM
 

Armando Prato said:

How about this in the same vein.... inconsistent sizes for the same column name that lives in different tables?

For instance, you may have a URL column in tableA and another in tableB.  However, their sizes differ... it's defined as varchar(255) in one and varchar(max) in another.

You could make a URL table with a FK constraint to it and define all URLs there.  That way you could keep the size defined in one spot.  Absent that, I have seen columns like these inconsistenly sized.

October 11, 2009 3:31 PM
 

Michael Coombes said:

Regarding your "Bad habits to kick" series of blog posts: -

Thank you.

I've discovered that I'm guilty of a surprisingly (to me at least) large number of these.

These are now printed out and stuck on the walls of my cube as a reminder of what not to do.

October 11, 2009 6:55 PM
 

Brian Tkatch said:

>Pick what works for you, but STICK WITH IT and ENFORCE IT.

So true. We also recently decided to use object_action for PROCEDUREs. I really wish SQL Server would copy Oracle's PACKAGEs though, so it becomes object.section, which is so much more intuitive (and inline with the .net model)

October 12, 2009 12:36 PM
 

C Rummel said:

An extension on column naming: try to avoid naming your id columns the same if they shouldn't be joined together (especially if the data types are identical.)  For someone brand new to an existing schema who does select <cols> from t1 inner join t2 on t1.someId = t2.someId <where> and doesn't get any data back, because t1.someId is "this" kind of id and t2.someId is "that" kind of id, then has to either look at the actual data in the table to know "which" id the columns really are (and hope the different id value domains don't overlap, else you could be getting data back that you shouldn't - which could be even worse) or find some documentation if it exists, or ask someone else if they're around and knows.  Student.Id vs. Class.Id may be obvious, but Student.Id vs. Person.Id vs. Employee.Id may not be.

December 3, 2009 11:39 AM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog

Syndication

Archives

Privacy Statement