Common Table Expression (CTE) in SQL SERVER

CTE: A common table expression (CTE) is a temporary result set that is defined during the execution of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.The CTE is available only for the duration of the query and is not stored like other objects in the database. Typical uses of CTEs are to replace views, group data, perform recursion, and create multiple references to a single table.

Syntax:

WITH <expression_name> [(column_name [,…n])]
      AS
(CTE_query)

 

Point to remember for creating and using nonrecursive CTE.

  • A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
  • Multiple CTE query can be defined in nonrecursive CTE,the definition must be combined one of the  UNION ALL, UNION, INTERSECT, or EXCEPT.
  • More than one With Clause is not allowed
  • We can not use ORDER BY except when a TOP clause is specified, INTO, OPTION with query hints clause.
  • A CTE can reference itself and previously defined CTEs in the same WITH clause.
  • Query referencing to the CTE can be used to defined a cursor.
  • Remote server table also can be used in CTE.

Recursive CTE:  a CTE can include references to itself. This is referred to as a recursive common table expression.

  • A recursive CTE contains at least two CTE  query definition and Anchor member and recursive member.
  • Number of column in Ancher and recursive member must be same.
  • DataType of column of anchor member must be same as datatype of corresponding column of anchor member.
  • FROM clause of a recursive member must refer only one time to the CTE Name.
  • CTE query definition does not allow GROUP BYSELECT DISTINCT,HAVING, LEFT JOIN, RIGHT JOIN, OUTER JOIN, TOP, SCALER AGGREGATION, SUBQUERIES.
  • A view that contains a recursive common table expression cannot be used to update data.
  • Cursors may be defined on queries using CTEs.

Examples:

You may also explore Subquery , nested subquery and derived table 

Difference between table variable and temporary table.