SQL Server Coding Standards: About Object Identifiers

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Prepared by Bihag Thaker

There are various types of objects in SQL Server like Tables, Views, Stored Procedures, User Defined Functions, Triggers, Constraints, Indexes, Schemas and Variables and so on. These objects should be assigned appropriate identifiers with which they are referenced in the database. Object Identifiers are of two types in SQL Server: (1) Regular Identifiers and (2) Delimited Identifiers

Regular Identifiers are the one that does comply with the format of identifiers in SQL Server. Delimited Identifiers are the one which does not comply with the format of identifiers.

Delimited identifiers must be delimited or enclosed by double quotation marks (“) or a pair of square brackets ([ ]).

For example, a delimited identifier not complying with the rules of the format of regular identifiers can be a T-SQL Reserved Word or can contain space within or any character which is not generally allowed in regular identifiers.

For example, even if ORDER is the reserved word in SQL Server, it can be assigned as an identifier to an object if it is delimited either by double quotation mark (“) or a pair of square brackets ([ ]) in the following manner in its definition

Delimiting identifiers with a double quotation mark (“) depends on SET QUOTED_IDENTIFIER setting of SQL Server.

If SET QUOTED_IDENTIFIER is set to OFF then all literal strings enclosed with a double quotation mark (“) are interpreted as string constants and not as identifiers. Due to this, delimiting identifiers with a double quotation mark (“) should never be used.

Following is an example of regular identifier assigned to a table which is not delimited:

Even if, SQL Server allows the use of delimited identifiers, its use is strongly discouraged and should notbe used. Use of regular identifiers to identify the objects should be practiced.

Following are some of the common guild lines along with best practices which follow the rules of regular identifiers and should be practiced while assigning identifiers to various objects:

Maximum length of identifiers should not exceed 128 characters and 116 characters for local
temporary tables.

First character must be a letter which can be any from either a-z or A-Z.

Even if SQL Server allows underscore (_), at sign (@) and number sign (#) as the first character, they should not be used as the first character in identifiers. Some of these characters have special meaning in SQL Server.

For example, an identifier that starts with a number sign (#) denotes a local temporary table, one that starts with ‘double number sign’ (##) denotes a global temporary table and one that starts with ‘at sign’ (@) denotes a variable name or parameter name.

Some of the functions in SQL Server starts with double at signs (@@). Thus, these special characters should be avoided while specifying identifiers.

The subsequent characters can be any letter or at sign (@), a dollar sign ($) or an underscore (_) character. However, use of these special characters in identifiers should be avoided. Underscore (_) character may be used for word separation.

Space should not be used in identifiers. For word separation, either an underscore (_) or Pascal Casing should be used. Wherever possible, usage of underscore (_) should be minimized by using Pascal Casing in identifiers.

For example, an identifier should be specified as ‘CustomerAddress’ instead of ‘Customer_Address’. It achieves the same readability by use of Pascal Casing and also reduces the length of identifiers.

Do not use T-SQL Keywords as object identifiers

Identifiers should be self-explanatory rather than being abbreviations and acronyms and they should indicate their purpose. This helps the developer in understanding the code written by someone else even if comments are not present.

For example, rather than using variable names @A or @i for ‘Sum’ and ‘Loop Counter’ respectively, they should be defined as @Sum and @Counter.