Use Consistent SQL Server Naming Conventions

By:   |   Comments (3)   |   Related: 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.

SQL Server 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" ...

SQL Server 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 when Naming SQL Server Objects

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 SQL Server 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 SQL Server 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, September 13, 2010 - 1:06:50 PM - Aaron Bertrand Back To Top (10157)
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 9, 2010 - 10:59:17 AM - Ryan Back To Top (10140)

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 9, 2010 - 9:34:34 AM - Dan Back To Top (10139)
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.















get free sql tips
agree to terms