15 December 2020

How to define a variable within a Microsoft SQL query

Sometimes a SQL query ends up needing an expression to be represented more than once. For example, some queries I've worked on have required a complex-to-calculate denominator that needs to be reused in multiple division calculations.

Usually one just duplicates the expression, but this is undesirable when:

  • the SQL for the expression is particularly long
  • or, particularly computationally expensive to calculate
I had long wished there was a way to embed a variable in the body of a select statement. Something like:

select
  t.numerator1 / denominator,
  t.numerator2 / denominator,
from
  mytable t
let
  demoninator = ....some complex calculation ...       -- 'let' is NOT valid SQL

Sadly the above is not valid SQL. However, it can be achieved as follows:

select
  t.numerator1 / v.denominator,
  t.numerator2 / v.denominator,
from
  mytable t
outer apply (
  select
     demoninator = ....some complex calculation ...
) v

Or more generally, this can be used with multiple variables, and the variables can be used in subsequent joins, and other query clauses.

select
  v.variable1,
  v.variable2,
  v.variable3
from
  mytable t
outer apply (
  select
     variable1 = ...,
     variable2 = ...,
     variable3 = ...
) v
join
  mytable2 t2 on t2.id = v.variable1
where
  v.variable2 = ...
order by
  v.variable3

There is an important caveat with this: the outer-apply manifests in the query execution plan as a nested loop join. However, this isn't necessarily a bad thing - rather it's representing the fact that the variables will always be calculated prior to other elements of the query being processed.

Note: don't go overboard with this method. But it can be a useful solution when duplication of expressions becomes problematic.