CTE(Common Table Expression) ?

Common Table Expression (CTE) is mainly used for following 2 features.

• Alternate to views, temporary tables
• Recursive queries. Especially this is very useful for data hierarchy queries where parent IDs and child IDs are in same table
• Disposable view. It can't work if u add some statement not related to CTE 

Reasons to use CTE

When should you use a CTE? Below are some good reasons when to use a CTE to resolve your TSQL statement requirements:

• If your query needs a recursive solution
• If your query has multiple subqueries, or you have repeated the same subquery code in a single statement
• If your query is large and complex

When your TSQL code is complex and/or contains one or more subqueries it become very hard to read. By breaking up your complex code into one of more CTEs you will make your code much easier to follow, maintain and debug.

Case 1: Simple CTE

With CTE_example
    AS
    (SELECT 100 Digit)
    SELECT * FROM CTE_example

We can also declare column names with CTE table declaration as shown below.

With CTE(Digit)
AS
(SELECT 100)
SELECT * FROM CTE

Output
Digit
-----------
100

Case 2: Declaring and using multiple CTEs

With CTE1(Digit_CTE1) 
AS
(SELECT 100 Digit)
 ,CTE2(Digit_CTE2)
AS
(SELECT 200 Digit)
SELECT * FROM CTE1 CROSS JOIN CTE2

Output

        Digit_CTE1    Digit_CTE2
        -------------------------
        100           200
    

Case 3: Recursive query using CTE

Without using recursive CTE query, it is not possible to display starts(*) in ascending order 5 times using one single query as shown below.

*
**
***
*****
*****

Recursive query to display starts(*) as shown above.

With CTE_Stars
AS
(select CONVERT(VARCHAR(10),'*') Stars
UNION ALL
SELECT CONVERT(VARCHAR(10),CTE_Stars.Stars+'*') Stars FROM CTE_Stars  WHERE  LEN(Stars)<6
)
SELECT * FROM CTE_Stars

Case 4: CTE Recursive query for data hierarchy (Parent Child hierarchy)

For this visit the link Recursive Data Hierarchy.

Limitations of CTE

1. Use select query of CTE in very first line immediately after CTE declaration.
2. We can use only one select query of CTE for one CTE declaration.
3. Sub queries and outer joins won't work within CTE declaration.
4. If there is a sequence of queries to be executed and if you want to use CTE query in between of them then the immediate query above CTE should end with semicolon;
as shown below.

                    select 'First query'
                    select 'Second query'
                    select 'Third Query';
                    With CTE(Digit)
                    AS
                    (SELECT 100)
                    SELECT * FROM CTE
            

If you remove ";" at the end of the query which is just above CTE then CTE query won't be executed and throws error.

Sample for real time CTE use

For example, create 2 tables as shown below to maintain student marks and college name.

CREATE TABLE Student_Results(name varchar(50),Subject varchar(40),Marks int)
insert into Student_Results values('Andrew','subject1',60)
insert into Student_Results values('Andrew','subject2',56)
insert into Student_Results values('Andrew','subject3',44)
insert into Student_Results values('Mark','subject1',74)
insert into Student_Results values('Mark','subject2',68)
insert into Student_Results values('Mark','subject3',98)
insert into Student_Results values('Steve','subject1',90)
insert into Student_Results values('Steve','subject2',86)
insert into Student_Results values('Steve','subject3',60)


CREATE TABLE Student_College(StudentName varchar(50),CollegeName varchar(40))
INSERT INTO Student_College values('Steve','Learners College')
INSERT INTO Student_College values('Andrew','Masters College')
INSERT INTO Student_College values('Mark','Arts College')

There are 3 students with their marks in three subjects. Another table has college name for each student. Below query using with CTE displays one row for each student with average marks and college name.

With CTE_Student(Name,AverageMarks)
AS(
SELECT name, avg(Marks) FROM Student_Results GROUP BY NAME)
SELECT CTE.Name,CTE.AverageMarks,C.CollegeName FROM CTE_Student CTE 
INNER JOIN Student_College C on CTE.name=C.StudentName

Output

Defining and Using CTE's

With the introduction of CTE's by Microsoft you now have a different way to build and document complex pieces of TSQL code. By using a CTE you can write and name a TSQL SELECT statement and then reference the named statement later on much like you would reference a table or a view.

Below is the syntax for defining a CTE:

WITH <expression_name> (Column1, Column2, …) AS (CTE Definition)

Where:

• "<expression_name>" is the name of the CTE
• "Column 1, Column2,…" are the names of the columns returned from the CTE (only required if column names are not provided, or are not unique)
• "CTE Definition" is a SELECT that defines the result set of the CTE.

A CTE definition needs to be followed by a single INSERT, UPDATE, DELETE, or SELECT statement that references the CTE. If the CTE is part of a batch then the statement prior to the WITH clause must end with a semi-colon. When you define multiple CTE's, a CTE that references another CTE needs to be define after the CTE it is referencing. This may sound confusing, but we'll see some examples shortly.

Below are some clauses that can't be used in a CTE:

• ORDER BY (unless used with TOP clause)
• INTO
• OPTION clause with query hints
• FOR XML
• FOR BROWSE

Summary

The CTE functionality was a great addition to SQL Server 2005. CTEs allow you to take your complex code and break it up into smaller more manageable pieces of code. CTEs also allow you to build recursive code. CTEs provides yet another method to implement complicate TSQL logic. Take the time to develop a few CTEs for your environment.

Question and Answer

In this section you can review how well you have understood using Common Table Expressions (CTEs).

Question 1:

When your CTE code is not the first statement in its batch, what must appear prior to the WITH clause of the CTE?

• a “GO” command
• a semicolon character
• a “END” keyword
• a “(“ character

Question 2:

If you define 4 CTEs to be used by a SELECT statement how many WITH clauses will be required to define the 4 different CTEs:

• 1
• 2
• 3
• 4

Question 3:

What is the maximum number of recursive CTE calls that can be made?

• 100
• 256
• 32, 787
• None of the above

Answers:

Question 1:

The correct answer is b. When a CTE is not the first statement in a batch, the statement prior to the WITH clause needs to be terminated with a semicolon.

Question 2:

The correct answers is a. The WITH clause is only specified 1 time when you have multiple CTEs that are used for a given query. The WITH clause precedes the first CTE definition. Each CTE definition that follows the first CTE definition needs to be preceded by a comma.

Question 3:

The correct answer is d. By default the MAXRECUSION default setting is 100. You can override this value by setting the MAXRECURSION option when you call your recursive CTE. When you use the MAXRECURSION option the limit is 32,787. If your CTE needs to be called recursively more than 32,787 times to complete, then you can set the MAXRECURSION to 0. Therefore there really is no maximum number of times a recursive CTE can be called.