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 (`)
|
-
If delimited identifiers are used when naming an object and the object name contains trailing spaces, SQL Server stores the name with the trailing spaces. Note that the semantics used to compare identifier names are the same as string comparisons. Therefore, trailing spaces are ignored. However, to avoid future compatibility issues, we recommend that you consider any character within the delimiter to be significant and that you consistently reference the object with its delimited identifier name.
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