Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 18 August 2015

CTE (Common Table Expression)

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


  1. 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
        

  1. 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


  1. 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)