When to use Common Table Expression (CTE)

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

An example of self referencing is recursion: Recursive Queries Using CTE

For exciting Microsoft definitionsTaken from Books Online:

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

• Reference the resulting table multiple times in the same statement.

I use them to break up complex queries, especially complex joins and sub-queries. I find I'm using them more and more as 'pseudo-views' to help me get my head around the intent of the query.

My only complaint about them is they cannot be re-used. For example, I may have a stored proc with two update statements that could use the same CTE. But the 'scope' of the CTE is the first query only.

Trouble is, 'simple examples' probably don't really need CTE's!

Still, very handy.

There are two reasons I see to use cte's.

To use a calculated value in the where clause. This seems a little cleaner to me than a derived table.

Suppose there are two tables - Questions and Answers joined together by Questions.ID = Answers.Question_Id (and quiz id)

WITH CTE AS(    Select Question_Text,           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers    FROM Questions Q)SELECT * FROM CTEWHERE Number_Of_Answers > 0

Here's another example where I want to get a list of questions and answers. I want the Answers to be grouped with the questions in the results.

WITH cte AS(    SELECT [Quiz_ID]       ,[ID] AS Question_Id      ,null AS Answer_Id          ,[Question_Text]          ,null AS Answer          ,1 AS Is_Question    FROM [Questions]    UNION ALL    SELECT Q.[Quiz_ID]      ,[Question_ID]      ,A.[ID] AS  Answer_Id      ,Q.Question_Text          ,[Answer]          ,0 AS Is_Question        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id)SELECT     Quiz_Id,    Question_Id,    Is_Question,    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as NameFROM cte    GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question order by Quiz_Id, Question_Id, Is_Question Desc, Name