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.

Try this technique on SQL Fiddle