When you have a query that does complex calculations, maybe even has function calls in the select list
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,
then you face the following dilemma:
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:
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:
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.