Apostrophes around column aliases

Far too often I see SQL code which uses apostrophes around column aliases, like this:

SELECT ProductID, SUM(LineTotal) AS ‘Total’
FROM Sales.SalesOrderDetail
GROUP BY ProductID;

This is fine, but the worrying thing about this is if the user decides to use this alias in an outer query.

SELECT ‘ID’, ‘Total’
FROM
(
SELECT ProductID AS ‘ID’, SUM(LineTotal) AS ‘Total’
FROM Sales.SalesOrderDetail
GROUP BY ProductID
) AS p
;

Here, the outer query will assume that ‘ID’ and ‘Total’ are strings, not the names of columns in sub-query. It’s really not pretty. The query runs, but doesn’t give the correct answers. Furthermore, if this had’ve been done in a GROUP BY clause, or a JOIN, etc, then the error may have been hidden some more. An error might have occurred, but only in certain circumstances.

What should have been done is to have used square brackets, like [Total], or even no brackets at all. Using the table alias in the outer query would have helped too.

SELECT p.ID, p.Total
FROM
(
SELECT ProductID AS ID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
) AS p
;

I’m not sure why SQL Server allows apostrophes to be used around column aliases, but unfortunately it does (and because it does now, it likely always will, if only to maintain backward-compatibility). So instead, any time you see code that uses apostrophes this way, please change it – just to help any developers that come after you who don’t understand where things can fall down.