A CTE is a temporary result set and are similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. A CTE is generally considered to be more readable than a derived table and does not require the extra effort of declaring a Temp Table . CTE is more powerful than a derived table as it can also be self-referencing, or even referenced multiple times in the same query.
CTE defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement
CTE can be referenced multiple ties in the same statement
WITH EmpCTE
AS ( SELECT EmpID, FirstName, LastName, ManagerID, 1 Level
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT EmpID, FirstName, LastName, ManagerID, Level+1
FROM Employee
INNER JOIN EmpCTE ON Employee.ManagerID = EmpCTE.EmpID
WHERE Employee.ManagerID IS NOT NULL )
SELECT *
FROM EmpCTE
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
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
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
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
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)
CTE
A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statements.
Form SQL Server version 2005 CTE is introduced.
A CTE can use used as to create a recursive query or as substitute of a view, where view is not required. Enable grouping by a column that is derived from a scalar sub select, or a function that is either not deterministic or has external access.
The CTE improves the readability of a complex query by dividing query into different logical blogs.
In addition CTE in SQL Server 2008, we can add the new MARGE function on it.
The syntax is mentioned bellow:
WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )
The statement to run the CTE is:
SELECT <column_list>
FROM expression_name;
We can call the CTE as we call a normal table from SELECT statements.
Example:
WITH salesCTE (SperID, NetSales)
AS
(
SELECT SperID, ROUND(SUM(SubTotal), 2)
FROM Sales.sale_OrdHdr
WHERE SperID IS NOT NULL
GROUP BY SperID
)
SELECT sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City + ', ' + StateProvinceName AS Location,
ts.NetSales
FROM Sales.sPerson AS sp
INNER JOIN salesCTE AS ts ON sp.BusinessEntityID = ts.SperID
ORDER BY ts.NetSales DESC
As we told that CTE can be recursive, the bellow example give us a outline of that
WITH
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r ON e.ManagerID = r.EmpID
)
SELECT FirstName + ' ' + LastName AS FullName,
EmpLevel, (SELECT FirstName + ' ' + LastName
FROM Employees
WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports
ORDER BY EmpLevel, MgrID
Hope this is interesting.
