solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Use consistent SQL Server Naming Conventions

MSSQLTips author Aaron Bertrand By:   |   Read Comments (3)   |   Related Tips: More > DBA Best Practices
Problem

I find it rare that a company possesses established and well-documented naming conventions. Of those that exist, it is tough to find a case where they are followed consistently. Not having or following a naming convention can lead to much frustration and difficulty in writing code around your database entities. This is especially important when your team is large and/or spread out as well as when multiple people are responsible for creating objects and writing modules.

Solution

Having documented naming conventions in place can be critical to the success of a project and even the success of a company. How your conventions are defined are completely up to you. They need to make sense to you and your team. They also need to be complete in order to follow them and benefit the organization. Let's review a few of the common areas where differences, in my opinion, can occur.

    Table and column naming styles

    Some people like lower case with compound words separated by underscore. Others prefer "Pascal Case" or "camel Case." So for a table that holds the addresses for customers, you may see:

      customer_addresses CustomerAddresses customerAddresses

    And for the columns, you may see:

      customer_id CustomerID CustomerId customerId customerID

    I prefer Pascal Case with no underscores, but I am not going to get into my reasons here. I am not trying to push my preferences onto you. I am simply trying to express the importance of having a naming convention and sticking with it.

    To find out whether you have been consistent, you can query the sys.columns system view to see if you have any variations of column names that you know are in several tables. For example, if you want to find out how many times you use a column name called "Customer_ID" vs. "CustomerID" vs. "customerId," you can run the following query (which converts the [name] column to a case-sensitive collation):

     
    ;WITH cols AS
    (
    SELECT [name] = [name] COLLATE SQL_Latin1_General_CP1_CS_AS
    FROM sys.columns
    WHERE [name] LIKE 'customer%id'
    )
    SELECT [name], COUNT(*)
    FROM cols
    GROUP BY [name];
    

    Case sensitivity may not be a priority for you, but I try to stay consistent. If you ever have to deploy to a case-sensitive environment, you will quickly learn that "CustomerID" != "CustomerId" ...

    Stored procedure naming styles

    As with tables and columns, I've seen many variations on procedure naming syntax. Consider a stored procedure to get the list of customers. You could have, among other things, these choices:

      Customer_GetList CustomerList GetCustomerList GetCustomers ListCustomers

    Stored procedure naming is important and code that references these procedures should be consistent. The last thing you want (and I've experienced) is someone creating a different version of the same procedure, because they use a different naming scheme, and did not find their version in the list. Now you have two spots in the application that are calling the same code, and when a bug fix or new feature is needed, the changes will need to happen in two places - but probably won't.

    Using prefixes

    One thing I see frequently is using prefixes to denote the object type or, worse yet, the data type. We're all aware that the "sp_" prefix for stored procedures is bad, right? While I have observed the industry auto-correct this situation for years, some people simply switch to using the superfluous prefix "usp_" or similar - this gets around the performance and predictability issues, but still defies any logic, since if all of your stored procedures start with "usp_" you haven't gained anything when parsing the list of procedures in any database. There are other prefix conventions that persist, such as using a "v" or "vw" prefix for views so you can tell them apart from tables (which may have a "t" or "tbl" prefix), or to have table-valued functions named in a certain way so they stand out from scalar UDFs. I also see plenty of systems where a developer has created a table with columns like "intCustomerID" and "strFirstName"; Hungarian notation is typically a no-no in these cases. Once again, if you have a really good reason to use these prefixes, I'm not going to try and stop you. But you should really think about what benefits they provide, and whether they are worth the refactoring cost you'll face if, for example, the data type of one of those columns changes, or you need to hide a table behind a view without touching the code that references the original table.

    Plural vs. singular table names

    I see both out in the wild. Personally, I prefer Employees over Employee. This is because the table holds a set, and it is very rare that a table will only ever have a single row (which the singular name implies). However, some prefer the singular because they only ever think of a single row at a time, or because it ensures that a table like Customer sorts before any related tables such as CustomerAddresses in Management Studio, source control and other IDEs. Again I am not trying to make you lean one way or another, just consider some of the reasons for picking a convention, and then be prepared to both enforce and defend your choice.

    Using schemas

    Microsoft's sample databases use multiple schemas for logical separation of their objects (e.g. all sales-related tables are in the Sales schema, and customers and employees are in the Person schema). In my primary service application I use schemas to separate logical functions, such as ETL vs. reporting vs. metadata. You may or may not choose to use this type of convention; most people in my travels continue to place all of their objects in the dbo schema, relying on the entity names to provide logical separation. Of course this doesn't work if you want to give your procedures names like "GetCustomerList" - since all the "Get" procedures for various entities will group together in the Object Explorer list. You should pick one or the other, in my humble opinion; either use schemas for logical separation, or make sure the entity name appears first. Otherwise, it will be very hard to isolate the modules surrounding a specific entity when we largely rely on alphabetical sorting.

Conclusion

There are a lot of opinions involved when coming up with naming conventions, and obviously there is not one single correct answer. If you want to properly standardize on a set of naming rules, you will have to come up with them on your own. Many choices will be easy, but you may end up fighting for others. This conflict is not the important part of the decision-making; more crucial is that, once the fight is over, you consistently follow your rules. It may seem like a lot of work up front, but I assure you it will pay off in the long run.

Next Steps


Last Update: 9/9/2010


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, September 13, 2010 - 1:06:50 PM - Aaron Bertrand Read The Tip
You may also find Vyas' naming cheat sheet helpful.  I don't necessarily agree with all of his conventions, but it is definitely a better starting point than anything I could assemble for you.

http://vyaskn.tripod.com/object_naming.htm


Thursday, September 09, 2010 - 10:59:17 AM - Ryan Read The Tip

Hi Dan,

Here's a sample naming conventions document that should get you started. Hope this helps. These are only guidelines,  you will have to add your own details to it. It has alot of samples in the document.

http://www.mssqltips.com/tipimages/2110_DB_Naming_Standards_20100916.pdf

--Ryan


Thursday, September 09, 2010 - 9:34:34 AM - Dan Read The Tip
Aaron - would you be willing to share your naming convention documentation? I'm working toward getting a consistent scheme set up in our organization and would appreciate anything you could provide.




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.