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.


1 comment:

yrh3ug015o said...

Additionally, they have a “hot and cold” slots characteristic which tells you which ones slots video games are paying out in the meanwhile. Ignition likes to keep issues easy, but practical with their banking options. They provide credit and debit card options properly as|in addition to} Bitcoin, Bitcoin Cash, bank wires, and checks as their suite of banking options. If you want 카지노사이트 to withdraw money from your online on line casino account, received't|you will not} obtain your earnings immediately, so count on a ready interval. This ensures a secure and authorized process between you and the operator.