T-SQL 101: 13 Quoting (Delimiting) Identifiers in T-SQL

If you look carefully at the following two queries in the image below:

you’ll notice there are a couple of differences. In the first query, the word Description is blue, and in the second, it isn’t blue but it has square brackets around it. The second is an example of quoting or delimiting an identifier.

In this case, the reason that the word was blue in the first query is that somewhere in SQL Server, this word is part of the syntax of the language itself. That’s much the same as SELECT or ORDER BY. So SQL Server Management Studio (SSMS) color-codes it the same way it does for the word SELECT.

This query would work fine without the quoting, but if I tried to have a column called ORDER or FROM, SQL Server would complain. I’d suggest that doing that is often a pretty lousy idea anyway. But if I did really want to do it, surrounding the name with square brackets would allow me to do it.

Now it’s not just reserved words where this applies. If I have an object name with spaces in the middle of it, I have to quote the name. For example [Ordered By] could be a column name. Generally I try to avoid spaced in column and other object names.

There are always exceptions

There are two exceptions to this though:

In dimensional data warehouses, I try to have non-key (i.e. displayable) columns human readable, and that means they’ll often have spaces in them. I don’t want to be renaming them before they’re added to every report, spreadsheet, Power BI dataset, analytic data model and so on.

The other situation is that if I’m using Service Broker, it’s pretty common to use names that look like URLs for the names of message types, and contracts. But that’s an advanced topic that we’ll have to talk about another day.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-04-15