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.
Delimited identifiers are used in these situations:
It is recommended that reserved keywords not be used as object names. Databases upgraded from earlier versions of Microsoft® SQL Server™ may contain identifiers made of words that were not reserved in the earlier version, but are reserved words for SQL Server version 7.0. You can refer to the object using delimited identifiers until the name can be changed.
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.
Types of delimiters used in Transact-SQL:
SELECT * FROM "Blanks in Table Name"
SELECT * FROM [Blanks In Table Name]
Quoted identifiers are valid only when the QUOTED_IDENTIFIER option is set ON. By default, the Microsoft OLE DB Provider for SQL Server and SQL Server ODBC driver set QUOTED_IDENTIFIER ON when they connect. DB-Library does not set QUOTED_IDENTIFIER ON by default. Regardless of the interface that is used, individual applications or users may change the setting at any time. SQL Server provides a number of ways to specify this option. For example, in SQL Server Enterprise Manager and SQL Server Query Analyzer, the option can be set in a dialog box. In Transact-SQL, the option can be set at various levels using SET QUOTED_IDENTIFIER, the quoted identifier option of sp_dboption, or the user options option of sp_configure.
When QUOTED_IDENTIFIER is ON, SQL Server follows the SQL-92 rules for the use of double quotation marks and single quotation marks in SQL statements:
To maintain compatibility with existing applications, SQL Server is not rigorous in its enforcement of this rule. Character strings can be enclosed in double quotation marks if the string does not exceed the length of an identifier; however, this practice is not recommended.
If the character string contains an embedded single quotation mark, insert an additional single quotation mark in front of the embedded mark:
SELECT * FROM "My Table"
WHERE "Last Name" = 'O''Brien'
When QUOTED_IDENTIFIER is OFF, SQL Server follows these rules for the use of double and single quotation marks:
If double quotation marks are used, embedded single quotation marks do not have to be denoted by two single quotation marks:
SELECT * FROM [My Table]
WHERE [Last Name] = "O'Brien"
Bracketed delimiters can always be used, regardless of the setting of QUOTED_IDENTIFIER.
The rules for the format of delimited identifiers are:
~ (tilde) | - (hyphen) |
! (exclamation mark) | { (left curly brace) |
% (percent) | } (right curly brace) |
^ (caret) | ‘ (apostrophe) |
& (ampersand) | . (period) |
( (left parenthesis) | \ (backslash) |
) (right parenthesis) | ` (accent grave) |
These examples use quoted identifiers for table names and column names. Both methods for specifying delimited identifiers are shown.
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "$Employee Data"
(
"^First Name"varchar(25) NOT NULL,
"^Last Name"varchar(25) NOT NULL,
"^Dept ID"int
)
-- INSERT statements go here.
SET QUOTED_IDENTIFER OFF
GO
CREATE TABLE [^$Employee Data]
(
[^First Name]varchar(25) NOT NULL,
[^Last Name]varchar(25) NOT NULL,
[^Dept ID]int
)
-- INSERT statements go here.
After the $Employee Data and ^$Employee Data tables are created and data is entered, rows can be retrieved:
SET QUOTED_IDENTIFER ON
GO
SELECT *
FROM "$Employee Data"
SET QUOTED_IDENTIFIER OFF
GO
-- Or
SELECT *
FROM [^$Employee Data]
In this example, a table named table contains columns tablename, user, select, insert, and so on. 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
)
If the SET QUOTED_IDENTIFIER option is not ON, the table and columns cannot be accessed unless bracket delimiters are used.
SET QUOTED_IDENTIFIER OFF
GO
SELECT *
FROM "table"
Here is the query result set:
Msg 170, Level 15, State 1
Line 1: Incorrect syntax near 'table'.
Here is the query using the square bracket delimiters:
SET QUOTED_IDENTIFIER OFF
GO
SELECT *
FROM [table]
Many system stored procedures, functions, and DBCC statements take object names as parameters. Some of these 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:
For single-part names, the string inside the single quotation marks represents the object name. If delimiters are used inside the single quotation marks, the delimiter characters are treated as part of the name.
If the name contains a period or other 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 owner name in addition to the object name. SQL Server requires that when a multipart name is used as a parameter, the entire string that constitutes the multipart name must 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 needed. For example, if a name part contains a period, double quotation mark, or 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]'
This example shows the same table name delimited with double quotation marks.
SET QUOTED_IDENTIFIER ON
GO
EXEC sp_help 'dbo."tab.one"'
GO
The table lists some of the Transact-SQL functions, DBCC statements, and system stored procedures that use multipart names.
Function or stored procedure name | Parameter name |
---|---|
COL_LENGTH | table |
DBCC CHECKIDENT | table_name |
DBCC CHECKTABLE | table_name |
DBCC DBREINDEX | database.owner.table_name |
DBCC SHOW_STATISTICS | table |
DBCC TEXTALLOC | table_name |
DBCC UPDATEUSAGE | table_name |
IDENT_INCR | table_or_view |
IDENT_SEED | table_or_view |
INDEX_COL | table |
OBJECT_ID | object |
sp_addextendedproc | procedure |
sp_autostats | table_name |
sp_bindefault | default object_name |
sp_bindrule | rule object_name |
sp_changeobjectowner | object |
sp_depends | object |
sp_dropextendedproc | procedure |
sp_fulltext_column | qualified_table_name |
sp_fulltext_table | qualified_table_name |
sp_help | name |
sp_helpconstraint | table |
sp_help_fulltext_columns | table_name |
sp_help_fulltext_columns_cursor | table_name |
sp_help_fulltext_tables | table_name |
sp_help_fulltext_tables_cursor | table_name |
sp_helpindex | name |
sp_helprotect | object_statement |
sp_helptext | name |
sp_helptrigger | table |
sp_procoption | procedure |
sp_recompile | table |
sp_rename | object_name |
sp_spaceused | objname |
sp_tableoption | table |
sp_unbindefault | object_name |
sp_unbindrule | object_name |
ALTER DATABASE | CREATE PROCEDURE |
ALTER PROCEDURE | CREATE RULE |
ALTER TABLE | CREATE TABLE |
ALTER TRIGGER | CREATE TRIGGER |
ALTER VIEW | Reserved Keywords |
CREATE DATABASE | SET QUOTED_IDENTIFIER |
CREATE DEFAULT |