What is the difference between square brackets and single quotes for aliasing in SQL Server?
I have seen some people alias column names using single quotes eg:
select orderID 'Order No' from orders
and others use square brackets eg:
select orderID [Order No] from orders
I tend to use square brackets. Is there any preference/difference?
Answer
To answer the question "is there any preference/difference":
Yes, there are as many preferences as there are opinions, but be careful whose preferences you adopt.
As a best practice, it is advisable to write portable SQL if it doesn't require any extra effort.
For your specific sample, it is just as easy to write a portable query...
select OrderId as "Order Id" from Orders
... as it is to write a non-portable one:
select OrderId as [Order Id] from Orders
It is preferable not to write non-standard SQL when there is an equivalent portable form of the same number of keystrokes.
The proliferation of [] for escaping is due to tools like SQL Server Management Studio and MS Access query builders, which lazily escape everything. It may never occur to a developer who spends his/her career in SQL Server, but the brackets have caused a lot of expense over the years porting Access and SQL Server apps to other database platforms. The same goes for Oracle tools that quote everything. Untrained developers see the DDL as examples, and then proceed to use the same style when writing by hand. It is a hard cycle to break until tools improve and we demand better. In Oracle, quoting, combined with mixed casing, results in case sensitive databases. I have seen projects where people quoted every identifier in the database, and I had the feeling I was in The Land of The Lost where the developers had evolved on an island without documentation or best practice articles.
If you write your DDL, from the start, with normalized, legal identifiers (use OrderId, or order_Id instead of [Order Id], you don't worry about the mythical keyword that might need escape characters; the database will inform you when you've used a reserved word. I can count on one finger the times we've ever upgraded an app from one version of SQL Server to another and had any breakage due to new reserved words.
This is often the subject of heated debate, so if you think about it another way:
C# programmers don't escape all their variables with @, even though it is legal to do so. That would be considered an odd practice, and would be the subject of ridicule on StackOverflow. Escaping should be for the edge cases. But the same developers that write conforming C# identifiers don't mind escaping every single identifier in their SQL, writing terribly ugly, non-portable SQL "code". As a consultant, I've met more than one SQL Server programmer who honestly thought [] was required syntax. I don't blame the developers; I blame the tools.