SQL Server Identifier

What is SQL Server Identifier?

Answer
Everything in a SQL server can have an identifier. Be it server, view, trigger, column etc. any database object name is an identifier. Identifiers may or may not be required for all objects.

Example:
CREATE TABLE table_name
(ID INT PRIMARY KEY
Note varchar(20));

Here, ID and Note are two identifiers for the columns. Constraints like Primary key need not have an identifier.

What are classes of identifier? Explain each class with an example i.e. regular and delimited identifier.

Answer
There are two classes of identifier. These identifiers must contain 1 to 128 characters.

Regular identifiers: they follow the rules for the format of identifiers. They are not delimited when used in transact SQL statements.

Example:
Select * from table_name Where ID =110

Delimited identifiers: they follow the rules for the format of identifiers. They may or may not be delimited when used in transact SQL statements. They are enclosed either in (“) or ([])

Example:
Select * from [table_name] Where [ID] =110

What are the Rules for Regular Identifiers?

Answer
Rules for Regular Identifiers:

These rules are dependant on database compatibility level. For a compatibility level 90, following rules may apply:

a. The first character must be of the following:

• Letters as defined by Unicode standard 2.0 including latin and other characters from other languages.
• Underscore (_), Hash (#) or at (@) symbol with each having a significant meaning.

b. Remaining characters can be letters (Unicode 2.0), decimal numbers, basic latin or other scripts, #, $,@ symbols.

c. Identifiers must not be keywords reserved by SQL.

d. Embedded spaces or special characters are not allowed.