Identifiers (Transact-SQL)

An identifier is a name that identifies a database object. For example, you can give names to tables, views, stored procedures, functions, triggers, and other database objects. In Transact-SQL, identifiers can be one-part or two-part names.

Two-part identifiers are used to qualify a one-part identifier with the name of the owner or the schema of the object. For example, dbo.MyTable, where dbo is the schema and MyTable is the table name.

Syntax

identifier ::= { <#identifier> | [ <#delimited_identifier> ] }

delimited_identifier ::= " <#character_string> " | [ <#bracket_delimited_identifier> ]

bracket_delimited_identifier ::= <#regular_identifier> ]
                

Arguments

An identifier must follow the rules for regular identifiers or delimited identifiers.

Regular Identifiers

Regular identifiers are not enclosed in quotation marks or square brackets. They must follow these rules:

• Start with a letter (a-z, A-Z) or an underscore (_).
• After the first character, they can contain letters, numbers (0-9), dollar signs ($), or underscores.
• Do not contain special characters, except for the underscore and dollar sign.
• Are not Transact-SQL reserved keywords.

Examples of regular identifiers:

MyTable
_MyVariable
Sales2023
Customer$ID
                

Delimited Identifiers

Delimited identifiers are enclosed in double quotation marks (" ") or square brackets ([ ]).

When you use delimited identifiers, you can:

• Include spaces and special characters.
• Use Transact-SQL reserved keywords as identifiers.
• Ensure that the identifier is treated exactly as you typed it, including case sensitivity (if the database collation is case-sensitive).

Examples of delimited identifiers:

"My Table With Spaces"
[Customer's Order]
"SELECT"
[123ID]
                

Rules for Two-Part Identifiers

A two-part identifier is used to specify the schema and the object name. The syntax is schema_name.object_name.

Both the schema name and the object name can be regular or delimited identifiers.

Examples:

dbo.MyTable
"Sales Schema"."Orders 2023"
[Production].[Product Cost]
                

If you do not specify a schema, SQL Server uses the default schema for the current user.

Reserved Keywords

Identifiers cannot be the same as Transact-SQL reserved keywords, unless they are delimited.

For a complete list of reserved keywords, refer to the Transact-SQL Reserved Keywords documentation.

Case Sensitivity

The case sensitivity of identifiers depends on the collation of the SQL Server instance and the database.

• Case-insensitive collation: MyTable, mytable, and MYTABLE are treated as the same identifier.
• Case-sensitive collation: MyTable, mytable, and MYTABLE are treated as distinct identifiers.

Best Practices

• Use meaningful and descriptive names for your identifiers.
• Follow a consistent naming convention across your database objects.
• Prefer regular identifiers over delimited identifiers for clarity and ease of use.
• If you must use special characters or reserved words, use delimited identifiers, but document their usage.
• Avoid excessively long identifiers.