Delimited Identifiers (Database Engine)

An identifier that complies with all the rules for the format of identifiers can be used with or without delimiters. An identifier that does not comply with the rules for the format of regular identifiers must always be delimited.

Note:

Microsoft SQL Server 2005 does not recognize variable names and stored procedure parameters that are delimited. These types of identifiers must comply with the rules for regular identifiers.

Delimited identifiers are used in the following situations:

When reserved words are used for object names or parts of object names.
Reserved keywords should not be used as object names. Databases upgraded from earlier versions of SQL Server may contain identifiers that include words not reserved in the earlier version, but that are reserved words for SQL Server 2005. You can refer to the object by using delimited identifiers until the name can be changed.

When you are using characters that are not listed as qualified identifiers.
SQL Server allows any character in the current code page to be used in a delimited identifier. However, indiscriminate use of special characters in an object name may make SQL statements and scripts difficult to read and maintain. For example, you can create a table with the name Employee], where the closing square bracket is part of the name. To do this you have to escape the closing square bracket using two more square brackets as shown in the following:

Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set to ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER to ON when they connect.

Regardless of the interface used, individual applications or users may change the setting at any time. SQL Server provides several ways to specify this option. For example, in SQL Server Management Studio, the option can be set in a dialog box. In Transact-SQL, the option can be set at various levels by using SET QUOTED_IDENTIFIER, the QUOTED_IDENTIFIER option of ALTER DATABASE, or the user options option of sp_configure.

When QUOTED_IDENTIFIER is set to ON, SQL Server follows the SQL-92 rules for the use of double quotation marks (")and the single quotation mark (') in SQL statements. For example:

Double quotation marks can be used only to delimit identifiers. They cannot be used to delimit character strings.
To maintain compatibility with existing applications, SQL Server does not fully enforce this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier. We do not recommend this practice.

Single quotation marks must be used to enclose character strings. They cannot be used to delimit identifiers.
If the character string contains an embedded single quotation mark, you should insert an additional single quotation mark in front of the embedded mark. For example:

If delimited identifiers are used when naming an object and the object name contains trailing spaces, SQL Server stores the name without the trailing spaces.

When QUOTED_IDENTIFIER is set to OFF, SQL Server uses the following rules for single and double quotation marks:

Quotation marks cannot be used to delimit identifiers. Instead, brackets have to be used as delimiters.

Single or double quotation marks can be used to enclose character strings.
If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks. For example:

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:

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.

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.

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 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.

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.