ou might expect all SQL Server’s clauses to support a column alias. As reasonable as that assumption seems, SQL Server can’t honor a column alias in that way. If you refer to an aliased column in another clause, SQL Server returns an error?surprise! Figure 1 shows the result of running such a query; SQL Server can’t find the aliased column TotalCost. That’s not a bug; SQL Server requires the alias?it has to call the column something!

SQL Server returns the error in Figure 1 because its engine works logically through the calls as follows:

1. SQL Server evaluates the JOIN (and any UNION operator) in the FROM clause. Imagine SQL Server combining the referenced tables into one table. SQL Server’s not retrieving data, just combining data sources.
2. SQL Server excludes whole records by applying a WHERE clause.
3. SQL Server applies the GROUP BY clause, which combines records when necessary, further limiting the results.
4. SQL Server applies a HAVING clause, in another attempt to limit the records the query retrieves.
5. At this point, SQL Server has identified the records to retrieve, and now uses the query’s SELECT clause to limit the columns.

Figure 1. SQL Server Engine Can’t Find TotalCost: This error occurs because SQL Server can’t find the aliased column.

?

Figure 2. The WHERE Clause Supports a Column Alias: ORDER BY is the only place SQL Server supports a column alias.

The same thing will happen with a GROUP BY or HAVING clause that refers to a column alias. SQL Server can’t apply an alias that it hasn’t defined yet. Outside of SELECT‘s column list, ORDER BY is the only place SQL Server supports a column alias (see Figure 2).

A Derived Table Solution

The solution to the column alias problem is a derived table. In this context, a derived table is similar to a derived column?it’s held in memory. Technically, a derived table is the result of using a SELECT statement in another SELECT statement’s FROM clause. You might know a derived table as a subquery. SQL Server successfully handles the column alias shown in Figure 3 by virtue of the derived table.

Figure 3. Use a Derived Table to Use a Column Alias: SQL Server successfully handles the column alias by virtue of the derived table.

Yes, you have to rethink your strategy a bit to get the results you want, but it’s a small price to pay for the convenience!

The derived table dtTotalCost retrieves all the columns the query needs, including the aliased (or derived) column. The main SELECT then handles the logistics of manipulating the data?any filtering, grouping, sorting, and so on. In this way, the column alias exists in memory (in the derived table) when the main SELECT‘s WHERE clause gets around to referring to it.

The rule is simple, even if it doesn’t appear so by glancing at the SQL:

1. Retrieve all the data into a derived table.
2. Let the main SELECT process the data (filter, group, sort, and so on).

Derived Solution

SQL Server’s engine processes a column alias at the end of a query. That means a column alias for a derived column isn’t available to other clauses. Instead of re-entering a complex expression, alias the column in a derived table. That way, the alias exists when the main SELECT gets around to referencing it.