Tag Archives: SQL

Sometimes we need to reduce the granularity of date ranges by combining serveral contiguous rows into one. While this is a common classic SQL problem, I was unable to find an elegant solution that also performs well, and came up with my own. This article explains the problem and outlines the solution using a Common Table Expression (CTE) with recursion in SQL Server 2005. Continue reading →

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.