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)
( 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)
SELECT n + 1
FROM Numbers
WHERE n < 10)

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.

Leave a Reply

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

You are commenting using your 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