Common table Expression (CTE)

What is Common table Expression

Common table expression (CTE) is a temporary result set and store the result set like normal view that can be use within a SELECT, INSERT, UPDATE, or DELETE statement. It’s also support to recursive.

The scope of the CTE is only a single query. That means that the single select, insert, update or delete statement work.

CTE must be use very next to declaration and argument is an optional.

WITH GetAllEmp(ID,Name)
AS
(
  SELECT EmpID,EmpName FROM Employee
)
SELECT * FROM GetAllEmp

OR

WITH GetAllEmp
AS
(
  SELECT EmpID,EmpName FROM Employee
)
SELECT * FROM GetAllEmp ;

Recursive CTE : their populating 100 records using recursive CTE

WITH GetRecur
AS
(
  SELECT 1 ID,newid()name
  union all
  select id+1,newid() from GetRecur b where b.id<100
)
SELECT * FROM GetRecur;

If recursion is more than 100 it will return error like

Msg 530, Level 16, State 1, Line 28
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

That case needs to set max limit like OPTION (MAXRECURSION <number of records>)

WITH GetAllEmp
AS
(
  SELECT 1 ID,newid()name
  union all
  select id+1,newid() from GetAllEmp b where b.id<1100
)
SELECT * FROM GetAllEmp  
OPTION (MAXRECURSION 1500)

If used where and group by then option comes on last side of statement

SELECT id,count(id) FROM GetAllEmp
where id <100
GROUP BY id
OPTION (MAXRECURSION 1500)

Note: union not allow in recursive CTE so using union all, its maximum 100 recursion limit and it need to more recursion then use option