Implement CTE (common table expression) queries

May include but is not limited to: recursive, non recursive

A common table expression is a powerful new tool which generates a named result set for the lifetime of the current query. In some cases, they can replace views (you don’t always need views), and therefore reducing complexity. They can be referenced by their name in the query multiple times, and they can be self-referencing, thus enabling recursion.

Here’s how a CTE looks like:

WITH Name of CTE
( List of columns)
AS
( SELECT statement )
Outer SELECT statement

This structure is fairly useful, but let’s see the real cool feature, the self-referencing and the recursion:

WITH Numbers (n)
AS
(SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Numbers
WHERE n < 10)
SELECT n FROM Numbers

The sample above, which originates from Michael Coles: Pro T-SQL 2008 Programmer’s Guide p.255, does nothing but lists the numbers from one to ten. But it does this in a recursive manner, which opens up new horizons in data access code.

A last thing to remember: CTEs have a recursion level of a hundred, by default. You can override this behavior by specifying the MAXRECURSION option, can be 0 to 32767.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s