Using SQL Server Column Aliases

When you have a query that does complex calculations, maybe even has function calls in the select list

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
from
       table_T

and you want to return the result of the calculation or function call, but also manipulate it further and return the result of this operation as well,

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
       result_A/result_B as result_ratio
from
       table_T

then you face the following dilemma:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'result_A'.

It is not allowed to reference named colums of a result set in the same query. The trivial workaround is to do the calculation or function call twice in the query:

select
       costly_function_A() as result_A,
       costly_function_B() as result_B
       costly_function_A()/costly_function_B() as result_ratio
from
       table_T

This would work but is not optimal because it nearly doubles the execution time (the same functions are evaluated twice). So what does SQL Server offer in terms of query-writing tricks that let us get around this issue?

There are 2 choices – neither is particularly elegant.

First, we can put the costly stuff into a derived table where it gets executed only once, and do the secondary calculation in an outer query:

select
       costly_T.result_A,
       costly_T.result_B
       costly_T.result_A/costly_T.result_B as result_ratio
from
(
       select
              costly_function_A() as result_A,
              costly_function_B() as result_B
       from
              table_T
)
as costly_T

Or we can use a view. If you do not already use views, don’t introduce one, but go with the derived table. If you do have views, you may already have the necessary layer of abstraction that is needed here.