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.
Table of Contents
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
- 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
- The First character must be one of the following
- 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.
- The underscore (
_), at sign (@), or number sign (#).
- Subsequent characters can include the following
- Any Letter as per Unicode Standard 3.2.
- May contain any number, character, or underscore The
@sign, dollar sign$, number sign#, or underscore_
- They cannot contain special characters or spaces
- You cannot use the reserved keywords
- Each identifier must have a unique.name.
- We must not use the Supplementary characters while naming them.
- 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
| Identifier | Invalid Reason |
|---|---|
| order | Because it is a Reserved keyword |
| emp name | spaces are not allowed |
| emp-name | – not allowed |
| 1result | cannot begin with a digit |
Example of Valid Identifiers
| empName | emp_name | _empName |
| result1 | result |
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.
1 2 3 4 5 6 7 | CREATE TABLE Order //Incorrect Syntax near Order Becuase it is a Reserved Keyword ( OrderNo [int] NOT NULL Default('0'), OrderDate Date NULL ) |
But, you can use the delimited format by enclosing it in a bracket [Order] as shown below
1 2 3 4 5 6 7 | CREATE TABLE [Order] //Reserved Keyword ( OrderNo [int] NOT NULL Default('0'), OrderDate Date NULL ) |
Examples of Delimited Identifiers
1 2 3 4 5 | Create table [Email Address] ( //Space in Table Name emailId varchar(10) ) |
1 2 3 4 5 | Create table [@Email Address] ( //Special Character emailId varchar(10) ) |
Naming Rules of Delimited Identifier
The Delimited Identifier’s must follow these rules.
- 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
- We must not use the Supplementary characters while naming them.
- 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.
- The Delimited Characters i.e
[]&""are not allowed.
References
Read More
