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:

--Nope
create table 1BadName
(
	 ID	int	identity(1,1) not null
	,Name varchar(50) null
);

--If you insist...
create table [1BadName]
(
	 ID	int	identity(1,1) not null
	,Name varchar(50) null
);

select * from [1BadName];

--Nope

create table 1BadName

(

ID int identity(1,1) not null

,Name varchar(50) null

);

--If you insist...

create table [1BadName]

(

ID int identity(1,1) not null

,Name varchar(50) null

);

select * from [1BadName];

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.

USE AdventureWorks2014;
GO

--will not drop 
if exists(select * from sys.objects where object_id = OBJECT_ID('Order_2017-10-23 23:39:37.880') and type = 'U')
	drop table [Order_2017-10-23 23:39:37.880];

--correct
if exists(select * from sys.objects where object_id = OBJECT_ID('[Order_2017-10-23 23:39:37.880]') and type = 'U')
	drop table [Order_2017-10-23 23:39:37.880];

--Making the table with datetime suffix
create table [Order_2017-10-23 23:39:37.880]
(
	 ID int identity(1,1) not null
	,OrderDate datetime2 default getdate()
);
go

insert into [Order_2017-10-23 23:39:37.880] default values;
go 3


select * from [Order_2017-10-23 23:39:37.880]; --yes
select * from Order_2017-10-23 23:39:37.880; --no
select * from "Order_2017-10-23 23:39:37.880"; --yes 

select OBJECT_ID('[Order_2017-10-23 23:39:37.880]'); --yes
select OBJECT_ID('Order_2017-10-23 23:39:37.880'); --null

USE AdventureWorks2014;

--will not drop

if exists(select * from sys.objects where object_id = OBJECT_ID('Order_2017-10-23 23:39:37.880') and type = 'U')

drop table [Order_2017-10-23 23:39:37.880];

--correct

if exists(select * from sys.objects where object_id = OBJECT_ID('[Order_2017-10-23 23:39:37.880]') and type = 'U')

drop table [Order_2017-10-23 23:39:37.880];

--Making the table with datetime suffix

create table [Order_2017-10-23 23:39:37.880]

(

ID int identity(1,1) not null

,OrderDate datetime2 default getdate()

);

insert into [Order_2017-10-23 23:39:37.880] default values;

go 3

select * from [Order_2017-10-23 23:39:37.880]; --yes

select * from Order_2017-10-23 23:39:37.880; --no

select * from "Order_2017-10-23 23:39:37.880"; --yes

select OBJECT_ID('[Order_2017-10-23 23:39:37.880]'); --yes

select OBJECT_ID('Order_2017-10-23 23:39:37.880'); --null

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.

--like 1st example this will not drop but it may look like it does because the statement successfully executes
declare @sql nvarchar(max);
set @sql = 'if exists(select * from sys.objects where object_id = OBJECT_ID(''Order_2017-10-23 23:39:37.880'') and type = ''U'')
	drop table [Order_2017-10-23 23:39:37.880];
';
--select @sql;
exec sp_executesql @sql;
select * from [Order_2017-10-23 23:39:37.880]; --table still here :(

--like 1st example this will not drop but it may look like it does because the statement successfully executes

declare @sql nvarchar(max);

set @sql = 'if exists(select * from sys.objects where object_id = OBJECT_ID(''Order_2017-10-23 23:39:37.880'') and type = ''U'')

drop table [Order_2017-10-23 23:39:37.880];

--select @sql;

exec sp_executesql @sql;

select * from [Order_2017-10-23 23:39:37.880]; --table still here :(

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:

--also correct 
if exists(select * from sys.objects where object_id = OBJECT_ID(quotename('Order_2017-10-23 23:39:37.880')) and type = 'U') 
     drop table [Order_2017-10-23 23:39:37.880];

select OBJECT_ID('[Order_2017-10-23 23:39:37.880]'); --yes
select OBJECT_ID(quotename('Order_2017-10-23 23:39:37.880')); --yes
select OBJECT_ID('Order_2017-10-23 23:39:37.880'); --null

--also correct

if exists(select * from sys.objects where object_id = OBJECT_ID(quotename('Order_2017-10-23 23:39:37.880')) and type = 'U')

drop table [Order_2017-10-23 23:39:37.880];

select OBJECT_ID('[Order_2017-10-23 23:39:37.880]'); --yes

select OBJECT_ID(quotename('Order_2017-10-23 23:39:37.880')); --yes

select OBJECT_ID('Order_2017-10-23 23:39:37.880'); --null

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:

Here it is in our example:

--works because default is set quoted identifier on
create table "select" ("identity" int identity not null, "order" int not null);
select * from select; --no
select * from "select"; --ok
select * from [select]; --ok
select * from quotename('select'); --no
select * from quotename('[select]'); --no
drop table "select";

--won't work
set quoted_identifier off
create table "select" ("identity" int identity not null, "order" int not null);

--works again
set quoted_identifier on
create table "select" ("identity" int identity not null, "order" int not null);

--database level setting - quoted identifier
select database_id, name, is_quoted_identifier_on
from sys.databases;

--works because default is set quoted identifier on

create table "select" ("identity" int identity not null, "order" int not null);

select * from select; --no

select * from "select"; --ok

select * from [select]; --ok

select * from quotename('select'); --no

select * from quotename('[select]'); --no

drop table "select";

--won't work

set quoted_identifier off

create table "select" ("identity" int identity not null, "order" int not null);

--works again

set quoted_identifier on

create table "select" ("identity" int identity not null, "order" int not null);

--database level setting - quoted identifier

select database_id, name, is_quoted_identifier_on

from sys.databases;

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!