Wednesday, April 29, 2015

Introduction

Don't Repeat Yourself (DRY) is an important principle of professional
programming practice (How's that for alliteration!) In SQL queries,
though, it is common to see repeated expressions. This article explores
how we can use the SQL APPLY operator to reduce repetition and make our
queries DRYer and easier to maintain.

Example of Repeated Expressions

A good example of repetition can be shown in the AdventureWorksDW2008R2 database. Consider the view [dbo].[vTimeSeries]:

There are three expressions in this query that are repeated. They are
given the aliases [ModelRegion], [TimeIndex] and [ReportingDate],
respectively. You can see these expressions first in the SELECT list
and later on in the GROUP by. We're going to eliminate this repetition
using the APPLY operator.

Using APPLY to Encapsulate Expressions

When you read the official documentation about the APPLY operator, it
is not immediately obvious how it can help us reduce repetition. (See "Using Apply" at https://msdn.microsoft.com/en-us/library/ms177634.aspx)
Many examples show how to use the APPLY operator to invoke a
table-valued function for each row in the rowset. However, since it is
possible to use SELECT queries in the APPLY context, it is also possible
to use an expression operating on columns in each row in the rowset.
How does this help us? Here's a simple example:

Note that the APPLY operator only contains an expression that uses a
column in the rowset and aliases the result. The alias is then used in
the main query. As an aside, note the use of '_' as the alias for the
APPLY rowset. This is handy when you don't care about the rowset alias
as in this case. It is a paradigm used in many programming languages as
well.

We can also use the results of one APPLY operation in a subsequent
APPLY. Note that the operations proceed top-to-bottom, left-to-right.
This means that the results of one APPLY are not available until the
operator appears in sequence. Let's expand our toy example a little:

Just like the toy example, above, the APPLY clause merely returns
aliased expressions using columns in the rowset. How will this make the
original query DRYer? We don't need to repeat these expressions!

Now, if the expressions need to change, we have only one place to change them. The query has become DRYer.

Summary

We can use the APPLY operator to encapsulate and alias expressions
that are repeated in our queries. This allows us to apply the principle
of "Don't Repeat Yourself" and make our queries easier to maintain.