CTE SQL Server

CTE is a temporary result set. It is not stored in memory. It can be used within a SELECT, INSERT, UPDATE, or DELETE statement or in View.It also
can be used in Merge statement. It exist only in same scope.

Advantage

• CTE improves the code readability.
• CTE provides recursive programming.
• CTE makes code maintainability easier.
• 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
• Substitute for a view when 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.
• It can be used instead of temp table or table variables in the stored procedures in the circumstances.
• CTE’s can also recursively refer to the same table using a union or union all, and this works great for searching an adjacency pairs pattern hierarch
• The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
• CTE can be defined in functions, stored procedures, triggers or even views.
• After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data

Syntax

Select Name,Age, employeeID    

From employee    
Where employeeID in    
(   
   Select employeeID from salary where salary >=1000 /******Sub Query******/   
)

Here the Select statement must be very next to the CTE. The name is mandatory and the argument is an optional. This can be used to give the alias to the retrieve field of the CTE.

The WITH keyword not only begins a CTE, it also adds a hint to a table reference. This is why the statement before a CTE must be terminated with a semicolon. 

We can use another CTE within a CTE but the query using the CTE must be the first query appearing after the CTE.

With salaryCTE(EmployeeID)  
  
AS  
  
(Select employeeID from salary where salary >=1000)  
  
, EmpDetailsCTE( Name, EmployeeID ,salary)  
  
AS  
  
(  
  
Select Name,Age, employeeID  
  
From employee Emp Join salaryCTE sa  
  
on Emp. employeeID = sa. EmployeeID)  

Types of CTE’s
Common Table Expressions can be placed into two broad categories:  Recursive CTE’s and Non-Recursive CTE’s


Disadvantage of CTE

• Firstly, CTEs cannot be nested like Subqueries.
• Secondly, CTEs cannot reference the main query; they are self-contained like the simple Subqueries. They may reference to any of the CTEs defined before it or even to itself.
• Are unindexable (but can use existing indexes on referenced objects)
• Cannot have constraints
• Persist only until the next query is run

When to Use CTE

Source : https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte

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