Donner’s Daily Dose of Drama

When you have 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 then you need to return the result of such a calculation, 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

the result is the following dilemma:

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

It is not allowed. Invoking the function 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

would work, but is not an option because it nearly doubles the execution time. 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.