Lara Kannan

Common Table Expressions (CTEs)

Why we need CTEs ?

You need to implement recursive queries to retrieve data which is presented in a hierarchical format. We normally resort to implementing views, cursors or derived tables and perform queries against them. The problem arises when the hierarchy level increases as SQL Server is limited to 32 levels of recursion.

We need a better way to implement recursive queries in SQL Server 2005 / 08. How do we do it? Use CTE.

Common Table Expressions (CTEs) are one of the most exciting features to be introduced with SQL Server 2005. CTEs come in 2 forms, non-recursive and recursive

A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

CTE can be used for both selects and DML statements. CTEs can also contain references to themselves. CTEs can also be used in place of views.

You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE.

1. Non-Recursive CTE

Non-Recursive CTE as a derived table, or a temporary view.

The concept behind a CTE is simplicity itself. Consider the following statement:

with MyCTE(x)
as
(select x = 'Kannan')

select x from MyCTE

In brackets after the 'as' keyword is the query that defines the CTE and it simply returning the string "Kannan".

---

2. Recursive CTE

To print the numbers 5 to 10 using SQL. How ? Before we go to actual code, let we see the syntex of it.

Syntex

WITH cte_alias (column_aliases) 
AS 
(
cte_query_definition   --initialization
UNION ALL
cte_query_definition2 --recursive execution
) 
SELECT * FROM cte_alias 

The table defined in the CTE can be referenced in the CTE itself to give a recursive expression, using union all.

DECLARE @Start INT
DECLARE @End INT
SET @Start = 5;
SET @End = 10;

WITH CTE AS
(SELECT @Start AS NUM
UNION ALL 
SELECT NUM + 1 FROM CTE WHERE NUM < @End
)

SELECT * FROM CTE

The query:

SELECT @Start AS NUM

is called the anchor member. This is executed in the first pass and will populate the CTE with the result, in this case 5. This initial CTE is repeatedly executed until the complete result set is returned. The next entry:

SELECT NUM + 1 FROM CTE WHERE NUM < @End

is a recursive member as it references the CTE, CTE. The recursive member is executed with the anchor member output to give 6. The next pass takes 6 as input and returns 7, and so on so that we arrive at a CTE populated with rows as follows:

5
6
7
8
9
10

The recursion will terminate when the recursive member produces no rows – in this case recursion stops when the length of x equals 10.

---

Note:

Note the ";" before the CTE definition – that's just a syntax requirement if the CTE declaration is not the first statement in a batch.

Disadvantage :

1. CTE will be available with the scope of the statement or block of code.
2. Compute, Order By (without a TOP), INTO, Option, FOR XML, and FOR BROWSE are all not allowed.

Hope this will help you. If so please add your comments. Thanks.