This is one of the most exciting features introduced in SQL Server 2005. A CTE is nothing but a “temporary result set" which is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It is important to note that the scope of a CTE is just the statement in which it is declared. The CTE named result set is not available after the statement in which it is declared and used. In many cases, CTEs can be used to replace derived tables, temporary tables and table variables.
WITH CTEName [ ( ColumnName[1..n] ) ]
( CTE_QueryDefiniton )
The QueryDefinition contains the Select Query which populates the CTE
Now in the above example, if you put one more select statement after the existing one, you will get the following error
Msg 208, Level 16, State 1, Line 6
Invalid object name 'MyFirstCTE'.
The following Query uses CTE to display the number of employees comes from a each city
Find all the Managers using CTE
Here comes the actual usage of CTEs. Suppose we need to list out all the employees reporting to a manager. In sql server 2000, you need to write a lot of stuffs to acquire this, but here this is very simple
As you can see from the above, the code consists of 2 parts, anchor member part and the recursive members part. The anchor member part just selects the managers or the Root level, while the other selects all the employees reporting to these manages in a recursive manner, so that the UNION ALL of these will yield the desired output.
The key word MAXRECURSION can be used as 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. To use it in our above example, include it on the last line;
OPTION (MAXRECURSION 3)
The above Query will return the employee levels upto 3. Also, if the value specified in the Maxrecursion part is less that the maximum recursion level, then you may also get the following error message, which specifies that the Query has terminated beore it completes.
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.
Here is the final example
- WITH’ clause requires that the statement preceding it in the Batch must be terminated by a ‘;’, unless it is the first statement of the batch.