Irregular Galaxy

Irregular Identifiers – Be Aware of Surprises

Surprises exist in SQL Server. There are “gotchas” to be aware of. One thing that can trip up your code is how tables, indexes, and other objects are named. They’re called identifiers. If you step outside the bound of a regular identifier then you must be careful how it is referenced.

For example adding a datetimestamp suffix to a table – let’s see how this can cause trouble.

NOTE: the featured image is an irregular galaxy taken by the Hubble Telescope

Image Credit: ESA/Hubble & NASA
Text Credit: European Space Agency

Identifiers

Identifiers are the name we give objects in SQL Server. Since mostly everything in SQL Server is an object this applies to tables, views, stored procedures, indexes, databases, columns, etc. When you create an object the name you give it is its identifier.

Identifiers can be broken down into 2 categories: regular and irregular. Let’s look at each.

Regular Identifiers

Regular identifiers are those which follow the rules SQL Server has for naming objects. There’s a list of them here.

To summarize:

  • The 1st character must be a character in Unicode a-z, A-Z, _, @, or #
    • There’s gotchas here too because some symbols are reserved naming conventions like @ for variable declaration and # for temp tables.
  • The following characters must contain Unicode characters a-z, A-Z, decimals, @, $, _
  • The identifier cannot be a T-SQL reserved word.
    • You can still name things after reserved words but you must use double quote marks or square brackets.
  • No embedded spaces or other special characters
    • You can still name things after reserved words but you must use double quote marks or square brackets.

Remember: you can still name things after reserved words but you must use double quote marks or square brackets.

An applicable theme here is:

Defaults are often poor and just because you can do something doesn’t mean you should!

Just because you “can” name a table “#MyMisleadingTableTitle” doesn’t make it a good idea.

Irregular Identifiers

An irregular identifier is simply one that does not conform the to standard regular identifier rules. Here’s some examples:

  • 1BadName
  • BadName&)!%
  • SELECT
  • #BadName
  • Bad Name

An example:

Most irregular identifiers are a bad idea. However, there are times when you might want to use one. For example appending the datetimestamp suffix to a table name like Order_2017-10-23 23:39:37.880.

Dynamic SQL can add more complexity here. Be careful when testing because your statements will likely parse and execute without errors (especially if you’re not using try/catch blocks) and you won’t know its buggy.

Important Settings for Identifiers

QUOTENAME

This function returns a string that is properly delimited. You can read about it on the BOL QUOTENAME page.

Let’s see how it deals with our example:

Using QUOTENAME is a possible solution here to referencing it in the OBJECT_ID function.

SET QUOTED_IDENTIFIER

The SET QUOTED_IDENTIFIER command enforces the rules for quote marks as delimiters for identifiers. Documentation for SET QUOTED_IDENTIFIER.

An option exists at the database level:

DB Property for Quoted Identifiers

Here it is in our example:

Summarizing Usage of Irregular Identifiers

If you’re going to choose object names in SQL Server that do not comply with typical regular naming conventions then be prepared for some unexpected tricks while your procedures run. Using square brackets is my preferred way of delimiting irregular identifiers. QUOTENAME works fine too. Also be mindful of the SET QUOTED_IDENTIFIER option. Follow these tips and you’ll be less likely to trip up.

Thanks for reading!

Did you find this helpful? Please subscribe!

Yes I want to Subscribe!

Email address goes here.

Leave a Comment