After one of my T-SQL presentations, I was asked about the significance of square brackets around objects in T-SQL. I had an idea of their purpose but to confirm I asked T-SQL guru, Itzik Ben-Gan.
Basically he highlighted the fact that a database object name is known as its identifier (e.g., a name of a column, table, etc.) and it can be either regular or irregular.
When the identifier is regular, delimiting it is optional but when the identifier is irregular (e.g., starts with a digit, has a space within it, is a reserved keyword, etc.), it has to be delimited.
T-SQL supports two kinds of delimiters:
- Square brackets, e.g., [Order Details], which is proprietary.
- Double quotes, e.g., "Order Details", which is standard.
|Both delimiters for irregular identifiers results in no errors|
|No delimiters for irregular identifiers results in errors|
If you want to know what makes an identifier regular then check here under “Rules for Regular Identifiers”.