• SQL Prompt
• SQL Refactoring

Removing the Square Bracket Decorations with SQL Prompt

For some, one of the more puzzling options in the SQL Prompt menu is the item ‘Remove Square Brackets‘. What square brackets and why remove them? What is their purpose and why are they square? These are all good questions.

Why use delimiters?

Identifiers, meaning the names of SQL Server metadata such as objects, aliases, columns and indexes, must conform to certain rules. If they don’t then you must delimit them.

The SQL standard specifies use of double quotation marks (“”) as delimiters. SQL Server inherits from Sybase a liking for the use of square brackets ([]) for the same purpose, though the double-quotes work fine too. The delimiters don’t become part of the identifier; they serve only to mark its beginning and end and are used only to tell the parsers what you intend.

You only rarely need to delimit or ‘quote’ identifiers. SQL requires you to delimit any identifier that is a reserved word, contains a character that isn’t allowed, or starts with a symbol reserved for a special object.

SQL allows only certain characters in an identifier. These characters include letters, decimal numbers, and a few symbols. An ordinary identifier must begin with a letter and contain only letters, underscore characters (_), and digits.

• A letter is defined by the Unicode Standard 3.2, and includes the Latin characters from a through z, from A through Z, and letter characters from the current collation.
• A digit means a decimal number from the current collation
• Allowable symbols include the @ (at) sign, $ (dollar) sign, # (number) sign or _ (underscore).

Why is there a problem with delimiters?

No experienced SQL Server developer would want to delimit identifiers unnecessarily and will avoid using both illegal characters and reserved words in their names. However, SQL Server choses to delimit all identifiers when you get a script via SSMS, regardless of whether it really needs it. It adopts this cautious approach just in case you are using an identifier that could, in future, become a reserved word. Almost all developers want to remove them, where possible, because they make SQL Code harder to read.

SQL Prompt provides the ‘Remove Square Brackets’ action to put right this quirk that SQL Server has with scripting.

Misuse of delimited identifiers

If you stick to the rules for naming objects, you’ll have little need for delimiters and your code is likely to be clearer. In the following example, I have allowed myself to use them, just to point out how quickly you can hit trouble or mischief. This code compiles and runs without complaint:

Having convinced yourself that it runs, but isn’t the sort of code you’d want to inherit, try using Prompt’s Remove Square Brackets action (either from the SQL Prompt menu, or Action list, or by using the keyboard shortcut, Ctrl+B, Crtl.+B). It can’t remove those pesky square brackets can it? If it did, the code wouldn’t run.

However, in SSMS, script out the Person.Person table from AdventureWorks, and you’ll quickly get a build script full of unnecessary square brackets:

This time when you run the Remove square brackets actions, all these delimiters will disappear, making it much neater and easier to read.

What about a quoted version?

Nope, nothing happens. SQL Prompt is only interested in square brackets, not delimited identifiers in general. This is useful because it means that you can use the standard quoted identifiers where they are really required, and then use Prompt to vacuum up all the square-bracketed identifiers produced by SSMS.

Why worry about quoted identifiers?

Any time that you produce SQL routines that require you to get metadata, you need to be prepared to delimit the names of the tables, columns, types, indexes and objects. I do a lot of this and I’m forced to use a couple of system routines that are either messy or faulty. The result is the same as with SSMS; you end up having to delimit everything.

The system procedure sp_validname is provided to check whether an identifier is valid. Unfortunately, it has a definition of ‘valid’ that is of no use for our purposes because it only considers an identifier to be invalid if it is wrong, even when delimited. This will only be the case if the identifier is blank, NULL or the Unicode replacement character, U+FFFF. It even fails to check for U+FFFF.

The QuoteName() function simply adds square bracket delimiters, and if the string already has a closing square bracket character in the string, it ‘escapes’ it. This is simply demonstrated, as follows:

To prove that the extra closing square bracket is merely ‘escaping’ the square bracket within the string, we can try using this bracketed expression to create a table:

This works fine:

We can even specify the table name to delete it:

If you ask SQL Prompt to remove square brackets, it sensibly leaves them alone in the above code.

A better ‘QuoteName’ for delimiting identifiers only when necessary

The answer to delimiting is, of course, to devise a way of checking an identifier, and only delimiting it when necessary. We ought, I suppose, to have the functionality of the poor shriveled sp_validname. I’ve done this as a function though it can easily be done with a procedure. It accepts two parameters, @TheIdeintifier and @TheDelimiter, with the latter being a double quote, by default, although you can set it to any valid delimiter.

I use this when I’m creating scripts directly from metadata, which can then be executed. You can never be sure that the names that you get from the metadata views will be valid, and so you need to check and compensate accordingly. Where you never see the code, then it is worth just ‘quotenaming’ everything with square brackets. After all, if it is never seen it does no harm. However, for anything that is ever seen, it is worth taking the extra trouble.

Conclusions

Whenever possible, you should avoid the need for having identifiers that aren’t valid SQL. That way, you avoid any misunderstandings or ambiguity. In some rare circumstances, you may need to introduce some invalid characters into identifiers, usually aliases. You may need to do it in column names, or preferably your aliases for them, to ensure that they are understandable in reports. Then you will need to delimit them.

Whenever we need to be able to see and understand SQL easily, we need to remove all surplus unnecessary delimiters. SQL Prompt has an excellent way of doing this. You’ll quickly convince yourself of this by generating scripts for tables in SSMS, and then running the Remove Square Brackets action. It is very satisfying to see the structure of a table appear from behind its decorations of square brackets.

 

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more