CTE (Common Table Expression)

Overview

• It is called Common Table Expression, introduced in SQL 2005
• It is kind of temporary result set with a scope of single DML operation (INSERT / UPDATE / DELETE)

• It will be stored in memory

• It will last only till the duration of the query

• It can be self referenced which is the best usage of CTE

• It is really useful in terms of recursion and deleting duplicate records

Advantages

• Improved readability

• Easy maintenance of complex queries

• Can be defined in views also

• Really works well for recursion and deleting duplicate records

Disadvantages

• Above statement should be enclosed with ;
• Cannot handle millions of records, #TEMP table will be a replacement in that scenario

Basic Syntax

WITH cte_name (optional column list)

AS

(

  Cte_query

)

  statement that uses the above CTE

Recursive Syntax

WITH cte_name (optional column list)

AS

(

  Cte_query --Anchor Member

Union All

Cte_query -- Recursive Member

)

  statement that uses the above CTE

Different Usages

1. Basic use to replace Table variable or #TEMP table

-- Creating Table

CREATE TABLE Employee (EmployeeID INT, Name VARCHAR(50), ManagerID INT, DOB DATETIME)

-- Inserting Records

INSERT INTO Employee

VALUES

(1,'Kamlesh',NULL, '1960-01-01')

,(2,'Pratibha',1, '1960-05-01')

,(3,'Maulik',1 ,'1980-01-01')

,(4,'Nisarg',1,'1983-01-01')

,(5,'Dipa',3,'1982-01-01')

,(6,'Swara',5,'2008-01-01')

,(7,'Megha',4,'1986-01-01');

-- Query

WITH StartsWithM

AS

(

SELECT Name FROM Employee WHERE Name LIKE 'M%'

)

SELECT * FROM StartsWithM

2. Using CTE for recurssion

-- Query  (Find out complete hierarchy of Swara)

WITH Hierarchy (EmployeeID, Name, ManagerID, Level)

AS

(

    -- Anchor Member Definition

    SELECT      EmployeeID, Name, ManagerID, 1 Level

    FROM  Employee

    WHERE EmployeeID = 6

    UNION ALL

    -- Recursive Member Definition

    SELECT      E.EmployeeID, E.Name, E.ManagerID, Level + 1

    FROM  Employee E

                INNER JOIN Hierarchy H ON H.ManagerID = E.EmployeeID

)

SELECT     

H.EmployeeID

, H.Name EmployeeName

, Level

, H.ManagerID

, ISNULL(E.Name,'<--No Manager-->') ManagerName

FROM        Hierarchy H

        LEFT JOIN Employee E ON H.ManagerID = E.EmployeeID

        

3. CTE within CTE / CTE with multiple reference

/* Find out the employees with their managers who born prior to 1985

 We can write without using CTE in a much simpler way but this is just an example of using one CTE inside other CTE */

WITH PriorTo1985

AS

(

SELECT        *

FROM        Employee

WHERE        DOB < '1985-01-01'

),

Manager

AS

(

SELECT        E.Name, ISNULL(M.Name,'No Manager') ManagerMame

FROM        PriorTo1985 P

INNER JOIN        Employee E

ON P.EmployeeID = E.EmployeeID

LEFT JOIN Employee M

ON E.ManagerID = M.EmployeeID

)

SELECT * FROM Manager

4. CTE within MAXRECURSION Hint

Maximum recursion is 100, if we want to go beyond that then we have to use MAXRECURSION Hint

If we don’t use it, by default it will take 100

We can give number from 0 to 32767

DECLARE        @startDate DATETIME,

@endDate DATETIME

SET                @startDate = '11/10/2011'

SET                @endDate = '03/25/2012';

WITH CTE AS

(

SELECT        YEAR(@startDate) AS 'yr',

MONTH(@startDate) AS 'mm',

DATENAME(mm, @startDate) AS 'mon',

DATEPART(d,@startDate) AS 'dd',

@startDate 'new_date'

UNION ALL

SELECT

YEAR(new_date) AS 'yr',

MONTH(new_date) AS 'mm',

DATENAME(mm, new_date) AS 'mon',

DATEPART(d,@startDate) AS 'dd',

DATEADD(d,1,new_date) 'new_date'

FROM        CTE

WHERE        new_date < @endDate

)

SELECT        yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'

FROM        CTE

GROUP BY mon, yr, mm

ORDER BY yr, mm

OPTION (MAXRECURSION 1000)