Six Tips For Avoiding Bad Table and Column Names

Do you like it when your schemata are easy to understand and maintain?  If so, keep the following in mind when choosing names for tables and columns.  If you're feeling evil and want to inflict some frustration on others, this might give you some good ideas too…

1.  Data types make horrible names

There's no rule that says you can't name a column after a datatype – it will just be awfully confusing.  The following code works perfectly:

1create table INT (
2   char datetime not null,
3   foo int null,
4   bit bit null,
5   timestamp datetime null
6);

The birth of the DATE datatype in SQL 2008 definitely throws a monkey wrench in the works as well.  How many columns do you know of that are named "Date"? (More on that in a bit.)  Again, it's not going to break anything, I just find it rather confusing when a column of type "datetime" is named "timestamp".

2.  So do reserved keywords

Interestingly, SQL Server datatypes are not found on the list of T-SQL Reserved Keywords.  The rules for T-SQL identifiers state that a regular identifier cannot be a T-SQL reserved keyword.  "Regular identifiers" are those which do not require brackets or double quotes around them.

 1-- This will fail
 2CREATE TABLE FOO (
 3  add CHAR(5),
 4  between INT,
 5  restore SMALLDATETIME
 6);
 7
 8-- Add brackets and it works just fine!
 9CREATE TABLE FOO2 (
10  [add] CHAR(5),
11  [between] INT,
12  [restore] SMALLDATETIME,
13  dump VARCHAR(10) -- DUMP is a reserved keyword but doesn't need brackets
14     -- probably because it is discontinued in SQL 2008.
15     -- maybe it should be removed from the list.
16);

3.  Pick a good name length

A good name should be long enough to be descriptive, but short enough that it's not a pain to type.  I hate columns or tables with names like "Date" or "Name".  Chances for confusion can easily be lowered by adding another word to make it more descriptive, such as "PurchaseDate" or "FamilyName".

Table and column information is stored in the sys.tables and sys.columns tables.  The name values are stored in columns of the data type sysname, which since SQL Server 7 has been equivalent to nvarchar(128). This is one of the cases where adding quotes or brackets can't help you break the rules. Names cannot exceed 128 characters in any case, and temporary tables are a special case as they can't exceed 116.

4.  Avoid spaces and special characters

They're allowed, but I consider it a bad practice to use them.  Most special characters are not included in the rules for regular identifiers, meaning that you'll need to enclose the name in double quotes or brackets.

1-- To be really tricky, you can start a name with a leading space!
2CREATE TABLE [test^one](
3   foo INT NOT NULL,
4   [ bar] varchar(10) NULL  -- This is evil
5);

5.  They can be case-sensitive depending on collation

Case sensitivity in object names depends on the database's collation settings.  If it is case-sensitive, then object names will be unique based on case sensitivity as well.

 1-- This will fail in a case-insensitive database
 2-- but runs fine in a case-sensitive one
 3create table testing1 (
 4   char datetime not null,
 5   foo int null,
 6   bit bit null,
 7   FOO int null,
 8   timestamp datetime null
 9);
10
11create table Testing1 (
12   char datetime not null,
13   foo int null,
14   bit bit null,
15   FOO int null,
16   timestamp datetime null
17);

6.  Don't pick names that will change meaning

The concept of a name that changes meaning might not make a whole lot of sense, so I'll elaborate with a short story.  In a previous job, we had to maintain a table that stored historical information for the previous 10 years.  Said table had 11 columns:  Key, Year0, Year1, Year2,…,Year9.

Year0 was always the current year, Year1 was last year, etc, so each year the columns changed meaning as far as what calendar year they really referred to.  There was also a special job that had to be run once a year to shift all the data one column to the right.  This is more than just bad naming, it's a horrible design to begin with!  We knew there was a much better way, but were stuck with this schema due to legacy application support.

In conclusion, a little thought when choosing table and column names can go a long way. I hope this is helpful!