By: Aaron Bertrand | Updated: 2010-09-09 | 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
- Review your current naming convention. If you don't have one, consider creating one.
- Remember that the specific standard you choose is not nearly as important as whether or not you are consistent in following that standard.
- Browse the following tips and other resources:
About the author
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
Article Last Updated: 2010-09-09
