What naming convention should I use in my database?

Unless you work with Celko, there really isn't a right or wrong way to name your database objects. Some ways are better than others, of course. There are three important questions to consider: 

• Does my convention make sense to me? 
   
  Don't laugh, but there are many cases where people develop something, go back a month later and can't figure out what the heck they've done. Having a poor naming convention can only add to the confusion, so it's important that you start with a good scheme, and think about what the scheme will mean to you if you revisit the code six months or six years from now. So an important goal here is that, no matter what convention you choose, you spend the time to document it. The exercise alone will make you both more familiar and more comfortable with your convention, and will improve your ability to convey the information to other people. 
   
  
• Will my convention make sense to other people? 
   
  There is nothing wrong with asking for input from other people, even if it's just a sanity check to confirm that your scheme makes sense. Most importantly, you should confer with people in your organization that will be most likely responsible for your system(s) in your absence—be it going on vacation, accepting another job, getting hit by a bus, what have you. Be prepared to defend your ideas, but be open to their input as well. Feel free, also, to run your convention by your peers, e.g. out in the public newsgroups. 
   
  
• Can I be consistent in implementing and enforcing my convention? 
   
  I've seen many systems where naming choices are poor and haphazard; usually, a mixture of implementation from various people. It's tough enough to reverse engineer another person's code, never mind when there a dozen people with their hands in there. Develop a good convention and stick with it—be sensible, because if you make your convention too complex, it will be difficult to be consistent in the long run, and it will be impossible to enforce its use among other people.

If you can answer yes to all three questions above, then you can't really learn anything from the rest of the article—but feel free to tag along. 
 
Rather than try and push my own conventions on you, I'm just going to go through the more common object types, tell you what I use (and why), and where necessary, tell you what I stay away from (and why). I'm anticipating a lot of flack here, so please remember, these are *my* conventions, and the justification of my conventions are merely *my* opinions. So please, no flames, no comments that my conventions are stupid, and no "my convention can beat up your convention" crap. If you feel any of these urges, please start over from the top of the page. I think I've made it very clear that this is not a "you need to do this" article, but rather a "this is what I do; learn from it if you like" article. 
 

---

 
General 
 
There are a few simple rules I live by when it comes to the name of any object in a database (including the name of the database itself): 

• Do not use spaces in object names. It might seem tempting and cute to have a view named "Sales By Quarter," but this is a nightmare to deal with programmatically. DTS, for example, will have serious problems, and anyone manually referencing the view will have to enclose it in [square brackets] to avoid an error. 
   
  
• Avoid using reserved words. This is easier said than done, as there is a very large list of current and future reserved words to check against (see Article #2080). Even if the word you use doesn't cause an error, it will still cause confusion when someone is editing code and Query Analyzer applies color highlighting to table or column names. 
   
  
• Do not use dashes in database names. Several SQL Server helper tools will complain about this; even the tools included with SQL Server 2005 are still unable to access such databases in certain cases. 
   
  
• Start object names with a letter. I see tables named "2005_Sales" and columns named "1", "2," "3," and so on (to represent months). Several applications out there will have serious problems with this. Go ahead, create this simple table, and try to access it through various interfaces (Enterprise Manager, Query Analyzer, DTS, VBScript, Visual Studio). Not too fun, huh? 
   
  

  CREATE DATABASE [foo-bar]  GO    USE [foo-bar]  GO    CREATE TABLE [dbo].[2005_Sales]  (      [1] INT,      [2] INT,      [3] INT  )

   
   
  
• Keep names short but meaningful. This is self-explanatory and fairly logical, but I can't count how many times I shake my head at a table name—either ridiculously long, making it cumbersome both to type and to commit to memory, or abbreviated to the point of utter confusion. "SlQ" is too short. "SalesFiguresForCompanyByFiscalQuarter" is just silly. There is a happy medium out there, I promise. 
   
  
• Use sensible case. There is little worse than going through a database schema where all the table names are in ALL CAPS—I feel like SQL Server is yelling at me! Likewise, trying to read a procedure name like "getallarticlestatisticsbyweek" could drive some people to drink. I like camel-/Pascal-casing or using underscores, leaning toward the former in most cases: GetArticleStatsByWeek or getArticleStats_ByWeek (often this will depend on whether I am designing a system from scratch, or inheriting an existing convention). However, I do find it sensible within T-SQL code to capitalize T-SQL keywords. Combined with proper indenting, this makes for really good visual cues--separating the T-SQL logic from the objects it is touching. But I'm going to stick with naming conventions here, and leave coding conventions for another day. 
   
  
• Avoid Hungarian notation. The name of the object should make it pretty obvious what type of data it contains, and if for some reason it does not, then there is always the metadata tables and/or the documentation you should have written when designing the system. Using datatype-style prefixes for columns like IContactID (integer) and VEmail (varchar) not only make the column names harder to read, they also make them less flexible. 
   
  Let's say IContactID exceeds the upper bound of the Integer data type, and we need to convert it to a BigInt. How do we do that? Do we change the column name and—simultaneously—all the code in all of our projects to reference the new name? Do we create a view with the old column name, rename the base table and give its name to the view? Do we leave the column name the same, instantly making the convention obsolete? 
   
  This borrowed concept from programming languages is not really necessary in the confines of a database, where you are dealing with much fewer variables, and data structures are fixed. 
   
  
• Always use the dbo. prefix. You will only be bitten by this once or twice until you make it a fundamental part of your database practices. When you are logged in as a non-dbo user, and you create a table without giving it an owner prefix, e.g.: 
   
  

  CREATE TABLE foo  (      Bar INT  )

   
  What can happen is that other users won't see it, because it is stored in the system as you.foo instead of dbo.foo or them.foo. If you consistently use the dbo. prefix, you will eliminate the possibility of creating the same object name twice, with different owner names. This can be a mess, especially when moving the database to a new server. Note that not specifying the owner/schema prefix when accessing objects in T-SQL queries will cause more work for the engine, as it will have to figure out WHICH table named "foo" you're talking about. It first tries <username>.foo, then will try dbo.foo. More performance impacts of leaving out the owner/schema prefix are outlined in this article.

---

 
Tables / Views 
 
Tables are sets of entities in the data model; as such, their names should be plural/collective nouns. The same goes for views: from a relational standpoint, they are just like a table, and represent sets of rows. The example I see Celko always talk about is a table representing employees. "Employee is bad, Employees is better, Personnel is best." Some people have this habit of singular table names, and their justification is either that you are describing a single row, or that it is possible that the table will have exactly one row (making the plural kind of silly). My answer to the first point has already been stated: you are describing a collection of rows, not a single row within that collection. And to the second point: there aren't many tables out there that have exactly one row, at least not for any tangible amount of time, and not likely on purpose. One example I can think of is a poorly-designed properties table for an app, e.g. 
 

CREATE TABLE dbo.Properties  (      BatchSize INT,      FolderPath VARCHAR(64),      LogFileName VARCHAR(64),      WakeUpTime SMALLDATETIME  )

 
However, even though there is only one row, you wouldn't call this Property, would you? So, maybe singular makes sense for a table with exactly one row and exactly one column. But then, why the heck is that a table, anyway? 
 
Like the Hungarian notation points above, there is an alarmingly common tendency to name tables with a "tbl" prefix. I don't find any valid reason for this convention, and while I could just say, "such a prefix is stupid," I'm sure people out there have their reasons. One might be to distinguish tables from views, however I don't see where this would really be necessary. The only thing I tend to distinguish between tables and views is using a v prefix on the latter, and even then, only when the view is not updateable. This just alerts me that the view is designed for SELECT only, and is much better than a "_notUpdateable" suffix. 
 
Now, I don't have a problem using *functional* and meaningful prefixes on table names. For example, Sales_Personnel and Support_Personnel. If these entities are really separate in your data model, then there is nothing wrong with naming them appropriately, so that access to them is unambiguous. A nice benefit, also, is that they will group together nicely in any GUI that presents the list of tables in alphabetical order. 
 

---

 
Columns 
 
Column names represent an attribute of the entity in your data model; so, they should be singular, and should reflect the entity, not the table name. There is no reason for this: 
 

CREATE TABLE dbo.Employees  (      EmployeeID INT PRIMARY KEY,      EmployeeFirstName VARCHAR(32),      EmployeeLastName VARCHAR(32),      EmployeeEmail VARCHAR(255) -- , ...  )

 
When this would suffice: 
 

CREATE TABLE dbo.Employees  (      EmployeeID INT PRIMARY KEY,      FirstName VARCHAR(32),      LastName VARCHAR(32),      Email VARCHAR(255) -- , ...  )

 
When referencing these columns, there is no reason to repeat the fact that they involve an employee. Even when joining against another table, you can pull these out as table_name.column_name or alias.column_name, so it is never ambiguous.  
 
However, there is one exception to my rule that you may have noticed: any column that is going to be referenced from other tables should contain the basic concept of what it is, so EmployeeID is in the Employees table as a primary key, and is also in the Payroll table as a foreign key. I see columns named "ID" all the time, and my knee-jerk reaction is to just rename it, and comment on why I did so using sp_addextendedproperty. :-) 
 

---

 
Stored Procedures and Functions 
 
My tendency with stored procedure names is to explain what they do in plain English, using a verb-noun scheme. Prefixes that tell you right off the bat what the procedure is going to do for you, like GetCustomerDetails, AddComment, EditArticle, RemovePermissions, ArchiveFileDetails. Remember, programmers and other non-DBAs will be using these procedures--they aren't likely to care about the actual database implementation (most end users don't need to know the difference between DELETE, TRUNCATE, DROP/CREATE, they just need to know that when they call EmptyLog, the Log table will thereafter contain 0 rows). My scalar and table-valued UDFs almost unilaterally start with the word "Get," because they are always used to retrieve something. 
 
Do not use the "sp_" prefix, for a couple of reasons. One is that sp_ procedures have a special meaning to SQL Server, and when calling it, the server will check the master database first to see if there is a system procedure with that name (and this can be a minor performance hit). Another is that sp_ is kind of a "reserved prefix"... Microsoft will continue to name their procedures this way, so even if you find the performance hit negligible and decide to use the prefix anyway, there is a chance that a service pack, hotfix or new version of SQL Server will introduce a new stored procedure with the same name as yours, and now the engine will call the one in master and your application will break. 
 
Another prefix to avoid is the "dt_", as this is used for procedures that are created with tools like Database Diagrams and Visual Studio. Having these prefixes won't cause any performance issues, as they are merely local to the database, however it is feasible that before the dt_ procedures are created, you will use a common name (causing a conflict). For more information about the dt_ procedures, see Article #2534. 
 
Similarly, do not use the "fn_" prefix for functions. While I am not aware of performance implications, there is certainly the possibility of your own functions getting lost within those that ship with SQL Server. 
 
The dbo. prefix is important when creating functions, because there are many cases where you simply can't call a function unless you prefix it with the owner name (this is not true with any other object). 
 
Like tables, functional prefixes like Reporting_ and Admin_ can be used to group functionally-related procedures and functions. This can help both in organizing your objects from a management perspective, and recognizing functional areas within T-SQL code where these objects are referenced. 
 

---

 
Triggers 
 
I usually put the name of the table in the trigger, prefixed by the action I want the trigger to perform. For example, PreventDuplicateEmployees would be a trigger that prevents the same employee from being inserted into the table multiple times (if, for some reason, constraints didn't or couldn't prevent that in the first place). PreventDuplicates wouldn't be very wise, because trigger names must be unique within the database, not the table—so I would have to adjust the name of the trigger if I wanted the same kind of activity on any other table. 
 
I don't find prefixes like tr_ or trig_ very useful here. I know I'm dealing with a trigger because I had to go through the Triggers node. I don't believe I have ever had to trace a reference to a trigger in T-SQL code, and try to figure out what kind of object it is, because triggers are not typically found in any code at all. 
 
It can be useful to include the triggering action of the trigger in the name, such as PreventDuplicateEmployees_Insert, but I don't find this essential. Even if the name doesn't make it clear what kind of action will spring it to life, usually if I am interested in the trigger, I am going to view the source code anyway, and right there is the FOR [INSERT][UPDATE][DELETE] statement. 
 

---

 
Indexes 
 
Like triggers, since indexes are not typically referenced explicitly in code (and such references do not need to be reverse engineered), there is little value in thinking too hard about the name applied. I tend to indicate whether the index is clustered, non-clustered, unique, non-unique, and which column(s) it applies to. So I might have a non-clustered index on Employees.LastName, Employees.FirstName, and it might be called NC_Employees_Name. Like several of the other object types, there is little use in prefixing with i or idx, because there just aren't enough scenarios where an index can be confused with any other kind of object. Unless, for whatever reason, you're trying to model a system by looking only at: 
 

SELECT OBJECT_NAME(id) FROM sysobjects

 

---

 
Keys, Defaults and other Constraints 
 
I don't have any hard and fast rules here. Usually, I don't have a need to know the names that the system sees attached to my keys and constraints, unless I need to disable or drop them programmatically. Which is usually an administrative task, not one which is performed from T-SQL code. Even then, I'm going to retrieve the name(s) from the metadata anyway, because it is unlikely that any convention can be assumed to have been followed. If I have violated a constraint and need to drop it temporarily, chances are, I already know the name because it is in the error message. 
 
So, I let the system name these things on its own. Instead of creating a table and *then* applying keys, constraints and defaults, I build the CREATE TABLE statement with the definitions for those elements included. 
 
If I were to recommend how to name these things, I suppose I would differentiate primary keys and foreign keys the same way the system does, however I would not include column names. This can get very cumbersome if you have a composite key, and even more so if you have several such references in one table. So maybe I would have PK_Employees as the primary key on the Employees table, and for EmployeeID in payroll, the foreign key would be named FK_Payroll_Employees. 
 
Defaults and constraints could have similar logic applied, but I do not advocate naming them with the values that are used. If I set a default value for a SMALLDATETIME column called TraceStart, I might use DF_TraceStarted. If I have a constraint on a column named salary, maybe it is C_Salary. Bad names in these cases would be DF_TraceStarted_GetDate and C_Salary_Between_0_And_500000; mainly because I can look at the object to determine what it is doing, and if I change the constraint I also have to change its name. Whereas the PK and FK are much less likely to undergo a change of that scope. 
 

---

 
Well, that's about it. I was going to get into other things like users, logins, user-defined data types, DTS packages, but then it started getting late. If you stick to my general suggestions above, most of these other things will just shape themselves.