Identifiers in T-SQL

From time to time, you’re going to be confronted with a SQL Server solution where you need manage column or table names that contain non-alphanumeric characters, like for instance space or percent, or even reserved keywords like “table” or “select”.

Object and column names (identifiers)

Almost every character except the alphanumeric ones (a-z and 0-9) are invalid in names for objects or columns (known as identifiers) unless you wrap them in brackets (“[” and “]“). In fact, a lot of words you mights want to use in names are already so-called reserved keywords in T-SQL.

As a rough guide, you can assume that a word is reserved if it changes colour to blue or pink in SQL Server Management Studio when you type it. For instance, the following statement isn’t going to compile properly and will return a syntax error:

CREATE TABLE #test (
    table        varchar(100) NOT NULL,
    column       varchar(100) NOT NULL,
    PRIMARY KEY (table, column)
);

The reason, obviously, is that SQL Server needs a set of reserved keywords to be able to interpret your query correctly. To make matters worse, as there are newer versions of SQL Server released, there will invariably be new features, many of which also introduce new keywords.

Using brackets for column names

The solution is to use brackets around the identifiers (the columns, in this case) to tell SQL Server which words are meant as keywords and which ones are actually identifiers. Here’s how to fix the previous query:

CREATE TABLE #test (
    [table]      varchar(100) NOT NULL,
    [column]     varchar(100) NOT NULL,
    PRIMARY KEY ([table], [column])
);

Quoted identifiers

Some people prefer to use double quotes instead of brackets. You can do this in T-SQL as long as you’ve enabled quoted identifiers. This is done using the SET statement.

SET QUOTED_IDENTIFIER ON;

The difference between QUOTED_IDENTIFIER being enabled or disabled can be clearly seen in this example:

SELECT a AS a_Without, "a" AS a_Quoted, [a] AS a_Bracketed
FROM (SELECT 123 AS a) AS subquery;

When quoted identifiers are enabled, all columns will return 123. If quoted identifiers are disabled, the second column, “a”, will be treated as a string literal instead of an expression, and will consequently return the string “a”.

As far as I know, the ANSI SQL standard is to allow quoted identifiers, so if you enable ANSI_DEFAULTS, quoted identifiers are automatically set as well. However, I think quotes are messy, and my personal recommendation is to make a habit of using brackets for identifiers and apostrophes (single quotes) for strings. That way, your code will not run into compatibility issues due to flag settings in the production environment down the road.

Escaping characters in identifiers

For the sake of argument, you could theoretically include brackets in identifiers, though I would in the strongest sense of the word advise against this. Say you have a column called “some[thing]else”. The following will fail:

--- Not bracketed - won't work:
SELECT 1 AS some[thing]else;

Trying to escape the identifier with brackets at the beginning and end would look like this:

--- Bracketed, but not escaped - won't work either:
SELECT 1 AS [some[thing]else];

This will also return a syntax error. The reason is that the first closing bracket terminates the identifier, which leaves SQL Server wondering what on earth you mean by the keyword “else” without a matching “IF”.

What you need to do is to escape the closing brackets using – you guessed it – closing brackets. A starting bracket doesn’t need to be escaped, but the closing bracket does.

--- This will work:
SELECT 1 AS [some[thing]]else];

The QUOTENAME() function

If you’re unsure of how to bracket your identifiers, you can use the built-in QUOTENAME() function as a guide. This is particularly handy if you have nested brackets. However, again, just don’t use brackets in identifiers if you have a choice.

SELECT QUOTENAME('some[thing]else');

-- returns [some[thing]]else]

Remember to subscribe or “like” the Facebook page, and check back next week for more T-SQL goodies!