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]) );
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!
One thought on “Identifiers in T-SQL”
Using brackets for identifiers and double quotes for strings will definitely get you into trouble down the road should you ever want to use your code anywhere beyond the Microsoft universe. Adhering to the standard is mostly possible (with a little extra work) with MS SQL Server and is to be strongly advised.
The standard is clear: Only single quotes are used for strings, and double quotes may be used for identifiers which don’t start with a letter or underbar (your example of an unquoted table name starting with # means trouble in any standard-adhering database) to allow other characters beyond ASCII (even in other alphabets unless you use MS SQL Server), match a reserved word or contain spaces. Additionally, quoted identifiers are case sensitive, while non-quoted ones aren’t.