CTE:Common Table Expressions

1)CTE's replace subqueries i.e they are result of complex queries so can be defined as temporary resultset.

2)CTE's are mainly used for recursive programming.

3)The lifetime of CTE's is the individual query it live in i.e current query.

4)Maintenance of complex and sub-queries and readability is improved by CTE's.

5)Using CTE's performance is not compromised until data sets are really huge.

6)CTE's are created in memory not in Tempdb database and indexes cannot be created on CTE's.

Syntax for CTE's:

WITH CTEname (column-aliases)
AS( CTE-query-definition(sub-query or complex-query) )

The statement to run the CTE is:

SELECT column-list FROM CTE-name;

Recursive CTE's has following syntax:

WITH CTE-name (column-aliases) 
AS 
(
    CTE-query-definition1   --initialization
    UNION ALL
    CTE-query-definition2 --recursive execution
)

The statement to run the CTE Resultset:

SELECT * FROM CTE-name;

A sub query example without CTE is given below :

There are two tables Employee and Address in database:

 SELECT * FROM (
 SELECT Addr.Address, Emp.Name, Emp.Age From Address Addr
 Inner join Employee Emp on Emp.ID = Addr.ID) Temp
WHERE Temp.Age > 50
ORDER BY Temp.NAME

By using CTE above query can be re-written as follows :

With Common(Address, Name, Age)
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.ID = Addr.ID
)

The statement to run CTE is:

  SELECT * FROM Common WHERE Common.Age > 50 ORDER BY Common.NAME.