Rules of SQL formatting – Regular and delimited T-SQL identifiers

In this article, the rules for delimiting identifies and show how to avoid adding unnecessary delimited T-SQL identifiers will be discussed, when using ApexSQL Refactor SQL formatter.

Square brackets in T-SQL are necessary if the name of an identifier does not comply with all the rules for the format of identifiers. Identifiers are classified into two classes: regular identifiers and delimited identifiers. Regular identifiers are complied with the rules of formatting and they do not have to be delimited. Delimited identifiers do not comply with the rules for the format of identifiers and are delimited with the quotation marks (“) or square brackets ([]). Delimited identifiers have special characters or reserved SQL Server word in name.

Rules for Delimited Identifiers

The rules for the format of delimited identifiers include the following:

• Delimited identifiers can contain the same number of characters as regular identifiers. This can be from 1 through 128 characters, not including the delimiter characters. Local temporary table identifiers can be a maximum of 116 characters
• The body of the identifier can contain any combination of characters in the current code page, except the delimiting characters themselves. For example, delimited identifiers can contain spaces, any characters valid for regular identifiers, and any one of the following characters [1]

Some examples of delimited T-SQL identifiers:

Identifier contains a special character:

SELECT *FROM [Email Address]   --Identifier contains a space

Identifier is a reserved word. SQL Server reserves both lower case and upper-case version of reserved word:

SELECT *FROM [Table]   --Identifier is a reserved keyword

Identifier uses both special characters and reserved words:

CREATE TABLE [My Table] – - Identifier uses a reserved keyword and contains a space

Identifier contains special characters:

SELECT *FROM [^$ProductionData] --Identifier contains special characters

Adding square brackets around all identifiers can be visually distracting. In some cases, identifiers will be unnecessary delimited by default.

When scripting an object in SSMS all identifiers will be delimited by default:

CREATE TABLE [Production].[Location] (
       [LocationID] [smallint] IDENTITY(1, 1) NOT NULL,
       [Name] [dbo].[Name] NOT NULL,
       [CostRate] [smallmoney] NOT NULL,
       [Availability] [decimal](8, 2) NOT NULL,
       [ModifiedDate] [datetime] NOT NULL,
) ON [PRIMARY]

In this case none of the identifiers needs to be delimited. If some of the T-SQL identifiers do not comply with the rules for the format of identifiers only those identifiers must be delimited:

CREATE TABLE Production.Location(
    [Location ID] SMALLINT IDENTITY(1, 1) NOT NULL,
    NAME dbo.NAME NOT NULL,
    CostRate SMALLMONEY NOT NULL,
    Availability DECIMAL(8, 2) NOT NULL,
    [Modified Date] DATETIME NOT NULL
)ON [PRIMARY];

When adding the columns from the Object Explorer in the SQL query, all identifiers will also be delimited by default:

In this case there is no need for delimiters. However, adding columns from some other table may require delimiting T-SQL identifiers:

Failing to delimit Database Version column name, in the previous example, would result with the syntax error:

SELECT 
DATABASE Version,
VersionDate,
ModifiedDate
FROM AWBuildVersion

Running this query will give:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword ‘Database’.

With ApexSQL Refactor unnecessary brackets from regular identifiers can be removed. Under the ApexSQL Refactor menu choose the Options command. This will open the Options window and under the Formatting tab select the Remove unnecessary brackets option:

Having the following code with both necessary and unnecessary brackets:

SELECT 
[Database Version],
[VersionDate],
[ModifiedDate]
FROM AWBuildVersion

ApexSQL Refactor will recognize T-SQL identifiers that need to be delimited, and remove brackets only where they are not necessary to avoid syntax mistakes:

SELECT 
[Database Version],
VersionDate,
ModifiedDate
FROM AWBuildVersion;

References:
[1] Books Online for SQL Server – Delimited Identifiers

Useful resources:
Books Online for SQL Server – Reserved Keywords (Transact-SQL)