SQL SERVER – 2005 Reserved Keywords

Microsoft SQL Server 2005 uses reserved keywords for defining, manipulating, and accessing databases. Reserved keywords are part of the grammar of the Transact-SQL language that is used by SQL Server to parse and understand Transact-SQL statements and batches. It is not legal to include the reserved keywords in a Transact-SQL statement in any location except that defined by SQL Server. No objects in the database should be given a name that matches a reserved keyword. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers.

Please use Microsoft MSDN BOL to see all the Reserved Keywords. The text above has been referenced from MSDN BOL.

How to Avoid Trouble With Reserved Keywords

The problem usually shows up late. A table works fine for months, then someone writes a new query and gets a syntax error near a column called Order or User. The column name was the issue all along.

Some reserved words that often sneak into designs are ORDER, USER, KEY, GROUP, INDEX, FILE and PERCENT. Management Studio colors many words blue, including some that are not reserved, so the color alone is not a reliable test.

If you are stuck with such a name, wrap it in square brackets, like [Order]. With QUOTED_IDENTIFIER on, double quotes work too. In dynamic SQL, the QUOTENAME function adds the brackets for you and handles names with odd characters safely.

My advice for new work is simple:

• Pick names that describe the data, like OrderDate or UserName, instead of a single keyword.
• Look at the list of future keywords as well. Words on that list may become reserved in a later release.
• Run your create scripts on a test database first. A reserved word fails right away with a syntax error, which is much cheaper than finding it in production.

If your code may run on more than one database platform, the documentation also has a list of ODBC reserved keywords worth avoiding. A name that is safe everywhere saves you from a painful rename later.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.