Delimited Identifiers (Database Engine)

Rules for Delimited Identifiers

The rules for the format of delimited identifiers include the following:

• Delimited identifiers can contain the same number of characters as regular identifiers. This can be from 1 through 128 characters, not including the delimiter characters. Local temporary table identifiers can be a maximum of 116 characters.

• The body of the identifier can contain any combination of characters in the current code page, except the delimiting characters themselves. For example, delimited identifiers can contain spaces, any characters valid for regular identifiers, and any one of the following characters.

  tilde (~)	hyphen (-)
  exclamation point (!)	left brace ({)
  percent (%)	right brace (})
  caret (^)	apostrophe (')
  ampersand (&)	period (.)
  left parenthesis (()	backslash (\)
  right parenthesis ())	accent grave (`)

The following examples use quoted identifiers for table names and column names. Both methods for specifying delimited identifiers are shown in the following:

SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE "$Employee Data"
(
 "^First Name"   varchar(25) NOT NULL,
 "^Last Name"   varchar(25) NOT NULL,
 "^Dept ID"   int
);
GO

-- INSERT statements go here.
SET QUOTED_IDENTIFIER OFF;
GO
CREATE TABLE [^$Employee Data]
(
 [^First Name]   varchar(25) NOT NULL,
 [^Last Name]   varchar(25) NOT NULL,
 [^Dept ID]   int
);
GO
-- INSERT statements go here.

After the $Employee Data and ^$Employee Data tables are created and data is entered, rows can be retrieved as shown in the following:

SET QUOTED_IDENTIFIER ON;
GO
SELECT * 
FROM "$Employee Data"
SET QUOTED_IDENTIFIER OFF;
GO
-- Or
SELECT *
FROM [^$Employee Data]

In the following example, a table named table contains columns tablename, user, select, insert, update, and delete. Because TABLE, SELECT, INSERT, UPDATE, and DELETE are reserved keywords, the identifiers must be delimited every time the objects are accessed.

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "table"
(
 tablename char(128) NOT NULL,
 "USER"    char(128) NOT NULL,
 "SELECT"  char(128) NOT NULL,
 "INSERT"  char(128) NOT NULL,
 "UPDATE"  char(128) NOT NULL,
 "DELETE"  char(128) NOT NULL
);
GO

If the SET QUOTED_IDENTIFIER option is not ON, the table and columns cannot be accessed unless bracket delimiters are used. For example:

SET QUOTED_IDENTIFIER OFF;
GO
SELECT * 
FROM "table";
GO

Here is the result set.:

Msg 170, Level 15, State 1
Line 1: Incorrect syntax near 'table'.

The following works, because of the bracket delimiters:

SET QUOTED_IDENTIFIER OFF;
GO
SELECT * 
FROM [table];
GO

Delimiting Identifiers with Multiple Parts

When you are using qualified object names, you may have to delimit more than one of the identifiers that make up the object name. Each identifier must be delimited individually. For example:

/* ISO quoted identifier syntax */
SELECT *
FROM "My DB"."My#UserID"."My.Table";
GO

Or

/* Transact-SQL bracketed identifier syntax */
/* Not available in SQL Server 6.5 or earlier */
SELECT *
FROM [My DB].[My#UserID].[My.Table];
GO

There are some special rules regarding how you delimit multipart stored procedure names in the ODBC CALL statement. For more information, see Calling a Stored Procedure.

Using Identifiers As Parameters in SQL Server

Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these parameters accept multipart object names, while others accept only single-part names. Whether a single-part or multipart name is expected determines how a parameter is parsed and used internally by SQL Server.

Single-part Parameter Names

If the parameter is a single-part identifier, the name can be specified in the following ways:

• Without quotation marks or delimiters

• Enclosed in single quotation marks

• Enclosed in double quotation marks

• Enclosed in brackets

For single-part names, the string inside the single quotation marks represents the object name. If delimiters are used inside single quotation marks, the delimiter characters are treated as part of the name.

If the name contains a period or another character that is not part of the character set defined for regular identifiers, you must enclose the object name in single quotation marks, double quotation marks, or brackets.

Multipart Parameter Names

Multipart names are qualified names that include the database or schema name and also the object name. When a multipart name is used as a parameter, SQL Server requires that the complete string that makes up the multipart name be enclosed in a set of single quotation marks.

EXEC MyProcedure @name = 'dbo.Employees'

If individual name parts require delimiters, each part of the name should be delimited separately as required. For example, if a name part contains a period, double quotation mark, or a left or right bracket, use brackets or double quotation marks to delimit the part. Enclose the complete name in single quotation marks.

For example, the table name, tab.one, contains a period. To prevent the name from being interpreted as a three-part name, dbo.tab.one, delimit the table name part.

EXEC sp_help 'dbo.[tab.one]'

The following example shows the same table name delimited with double quotation marks:

SET QUOTED_IDENTIFIER ON 
GO 
EXEC sp_help 'dbo."tab.one"'
GO 

See Also

Reference