When to use Common Table [removed]CTE)

Question

I have begun reading about Common Table Expression and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well? Can someone give me a simple example of limitations with regular select, derived or temp table queries to make the case of CTE? Any simple examples would be highly appreciated.

Answer 1

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 CTE
WHERE 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 Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name

Answer 2

It is very useful when you want to perform an "ordered update".

MS SQL does not allow you to use ORDER BY with UPDATE, but with help of CTE you can do it that way:

WITH cte AS
    (
        SELECT TOP(5000) message_compressed, message, exception_compressed, exception
        FROM logs
        WHERE Id >= 5519694 
        ORDER BY Id
    )
UPDATE  cte
SET     message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)

Look here for more info: How to update and order by using ms sql

Answer 3

One of the scenarios I found useful to use CTE is when you want to get DISTINCT rows of data based on one or more columns but return all columns in the table. With a standard query you might first have to dump the distinct values into a temp table and then try to join them back to the original table to retrieve the rest of the columns or you might write an extremely complex partition query that can return the results in one run but in most likelihood, it will be unreadable and cause performance issue.

But by using CTE (as answered by Tim Schmelter on Select the first instance of a record)

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

As you can see, this is much easier to read and maintain. And in comparison to other queries, is much better at performance.