SQL Server Common Table Expression

Common Table Expression

Common table expression is intermediate (temporary) result-set on which we can do DML operation.

Scope of CTE is next to the definition of the CTE object.

Usage of CTE

• CTE can be used to created recursive query like getting the company hierarchy from a self referencing table.
• Delete duplicate data from a table.
• Solve complex grouping and ranking needs and result-set based on that. 

Limitations

• We can not use INTO, ORDER BY, OPTION
• CTE can be followed by one DML statement which can reference CTE.
• Query referenced by CTE can not be used to define cursor.

Syntax

WITH <CTE_NAME> AS 

(

CTE_QUERY

)

DML_Query_Ref_CTE

Queries on common table expression

• From Employee table with columns emp_id, emp_name, manager_id find the employee hierarchy of the company. 
• From product table with columns prod_id, prod_name, price, last_update. Delete the duplicate products by keeping only the ones with most recent last_updated date.