Regular & Delimited Identifiers in SQL Server

Identifiers are the name we give to the Servers, Databases, and Database objects, such as Tables, Views, Columns, Indexes, Triggers, Procedures, Constraints, and Rules, etc. They must uniquely identify them. We then use the identifier to refer to the server, database, table whenever we want to use them. There are certain rules & restrictions that we must follow when we choose the name for the identifier.

Identifiers in SQL Server

There are two kinds of identifiers in SQL Server. One is a Regular identifier and the other one is the Delimited Identifier. The Regular Identifier must follow all the naming rules. While a Delimited Identifier does not have to follow any of those rules but we need to enclose it in double quotation marks (“) or brackets ([ ]).

Regular Identifier

The Regular identifiers names must follow all the following rules.

Naming Rules of Regular Identifier

  1. The maximum length cannot be more than 128 characters. Except for local variables & names of temporary tables that can have a maximum of 116 characters
  2. The First character must be one of the following
    1. Letter as per the Unicode Standard 3.2. For example, Latin characters a through z & A through Z. The letters from other languages also allowed.
    2. The underscore (_), at sign (@), or number sign ( #).
  3. Subsequent characters can include the following
    1. Any Letter as per Unicode Standard 3.2.
    2. May contain any number, character, or underscore The @ sign, dollar sign $, number sign #, or underscore _
  4. They cannot contain special characters or spaces
  5. You cannot use the reserved keywords
  6. Each identifier must have a unique.name.
  7. We must not use the Supplementary characters while naming them.
  8. The case-sensitivity of the identifier depends on collation. For example, You can create two tables with names that differ only in the case in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation.

Example of invalid Identifiers

IdentifierInvalid Reason
orderBecause it is a Reserved keyword
emp namespaces are not allowed
emp-name– not allowed
1resultcannot begin with a digit

Example of Valid Identifiers

empNameemp_name_empName
result1result

Delimited Identifiers

Delimited Identifiers do not comply with all the rules of the regular identifier. To use them you must enclose them in double quotation marks (“) or brackets ([ ]).

For Example the following Create Table throws error as Order is a reserved keyword in SQL Server.

But, you can use the delimited format by enclosing it in a bracket [Order] as shown below

Examples of Delimited Identifiers

Naming Rules of Delimited Identifier

The Delimited Identifier’s must follow these rules.

  1. The maximum length cannot be more than 128 characters. Except for local variables & names of temporary tables that can have a maximum of 116 characters
  2. We must not use the Supplementary characters while naming them.
  3. The case-sensitivity of the identifier depends on collation. For example, You can create two tables with names that differ only in the case in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation.
  4. The Delimited Characters i.e [] & "" are not allowed.

References

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top