Doctor Strangedata, Or How I Learned to Love the Common Table Expression

SQL Server 2005 offers a plethora of innovative technologies designed to let everyone access data in a way that would make the information more useful to each individual. The purpose, of course, is to ensure that the developer can use the information in one manner, while the administrator can manipulate it into another, similar, but different manner, while the common consumer of that same data is able to receive the information in a manner that is truly relevant to them.

That is the impetus behind the new technologies of SQL 2005, and from within comes one of the most powerful features that you may never have heard of; the Common Table Expression.

A Common Table Expression is really nothing more than its name implies. A simple manner in which to expression a common set of data in tabular form. While it may be described in such a simplistic manner, it is truly far more powerful than the description would imply. When I first began writing this article I had lofty plans for comparing execution plans written with and without a Common Table Expression, exploring the singular points of advantage of the CTE and so on. However, my manager, my teammates and others have been pointing out that while of this may be well and good, it misses the entire point of the article. This article is intended to expose the Common Table Expression, to bring this technology forward and let the people see what it is and what it can do.

So, what is a Common Table Expression? SQL 2005 Books Online describes a Common Table Expression as “A temporary named result derived from a simple query and defined within an execution scope.” Real people translation; “A way for a data to be accessed within the same query.” Let’s look at the structure of a CTE to see how it really looks:

WITH Expression_Name (Column 1, Column 2, Column n)

AS

{

     SELECT Column 1, Column 2, Column n

     FROM Table

}

SELECT Column 1, Column 2, Column n

FROM Expression_Name;

GO

The true power behind the Common Table Expression lies within its ability to present data in a manner that can be easily accessed through recursion. In previous versions of SQL, performing a recursive query was tedious at best and most often painful. However, the CTE provides a simple and efficient way of creating a common data set that can be easily recursed within the same query. Here is how it works:

WITH Expression_Name (Column 1, Column 2, Column n)

AS

{

       --Create Anchor

       SELECT Column 1, Column 2, Column n

       FROM Table

UNION ALL

       --Create Members

       SELECT Column 1, Column 2, Column n

       FROM Table

}

SELECT Column 1, Column 2, Column n

FROM Expression_Name

INNER JOIN Table

       ON Expression_Name.Column 1 = Table.Column 1;

While Books Online for SQL Server 2005 may present the Common Table Expression adequately for those with a certain amount of foreknowledge of SQL server and Transact-SQL programming, in my humble opinion it falls short of disseminating its secrets at a truly introductory level. This article is my attempt to fill that void and create a rudimentary example of the CTE in a manner in which someone with little or no SQL experience can understand the potential of the Common Table Expression, or at the very least, present the material in such a way that the concept can be understood by all audiences. What follows is an attempt at creating a pseudo-code example of how the Common Table Expression would work in a practical sense:

With Drive(Letter, Folder, File, Level)

AS

{

       SELECT Letter, Folder, File, 0 AS Level

       FROM

OperatingSystem.HardDrives AS Drive

       UNION ALL

       SELECT Letter, Folder, File, Level + 1

       FROM

OperatingSystem.HardDrives AS Drive

}

SELECT Letter, Folder, File, Level

FROM Drive

INNER JOIN Drive AS nD

       ON Drive.Letter = nD.Letter;

GO

Assuming the tables were created and populated, this would essentially mimic the contents of an Operating System file and folder list in a tabular format. It should look something like:

That is the magic of the Common Table Expression, to make a query infinitely more usable within itself. In many cases you can virtually eliminate the need for temporary tables and table variable simply by using a Common Table Expression. Not only is it more robust and powerful, but a CTE is far more resource efficient than the alternatives.

Read through the examples from Books Online. Learn to adapt the Common Table Expression into your every day coding practices and I guarantee you will never regret the decision.