SQL Server Common Table Expression:

Many developers often find difficult to write a complex query using Joins.Many Complex queries written using sub queries or joins can be simplified by using a technique called Common Table Expression Often called as CTE. I ll try to cover most of the details related to CTE in this post.

Most of the time, a sub query which fetches a set of data will have to be used at many instances in a one single query and hence writing it by using CTE will not only improves the performance but also the ease of writing SQL's.

CTE is standard ANSI SQL standard.CTE are oftentimes used during recursive process in which the query executes itself. CTE can also be called as a tremporary table and can be referenced within Select,Insert,Update and Also Delete statements.

CTE are of two types

• 1>  Recursive CTE
• 2> Non Recursive CTE

CTE are constructed with three core parts 

•  CTE Name (This is always followed by a WITH clause/Keyword)-Mandatory
• Column Names or the List of columns - Optional
• Query (This appears with a small parenthesis after the keyword AS)-Mandatory

Common Table Expression Or the CTE are represented as below

Advantages Of CTE:

• Referred as a table multiple times within a query.
• Mainly used in building recursive queries.
• Readability is increased.
• Can be referred as view
• CTE helps in dividing a query into simpler blocks or chunks of data and these chunks can be used as a building blocks for complex queries.

Examples For Recursive and Non recursive CTE's

---Non Recurssive CTE

WITH Vendors(V_Id, V_Sales)

as

(

  SELECT S.SalesPersonID, COUNT(*)

  FROM Adventureworks.Sales.SalesOrderHeader S

  WHERE S.SalesPersonID IS NOT NULL

  GROUP BY S.SalesPersonID

)

SELECT V.FirstName, Cte.V_Sales

FROM Adventureworks.Sales.vSalesPerson V

INNER JOIN Vendors Cte ON V.SalesPersonID=Cte.V_Id

ORDER BY Cte.V_Sales

-----Example For Normal CTE

IF OBJECT_ID('RecurssiveCte', 'U') IS NOT NULL

DROP TABLE dbo.RecurssiveCte

GO

CREATE TABLE dbo.RecurssiveCte

(

  Id int NOT NULL PRIMARY KEY,

  FirstName varchar(50) NOT NULL,

  SurName varchar(50) NOT NULL,

  MgrID int NULL

)

GO

INSERT INTO RecurssiveCte VALUES (101, 'Kiran', 'Kashyap', NULL)

INSERT INTO RecurssiveCte VALUES (102, 'Lalitha', 'Kashyap', 101)

INSERT INTO RecurssiveCte VALUES (103, 'Suhas', 'Kashyap', 101)

INSERT INTO RecurssiveCte VALUES (104, 'Gayathri', 'Kashyap', 102)

INSERT INTO RecurssiveCte VALUES (105, 'Guru', 'Darshan', 102)

INSERT INTO RecurssiveCte VALUES (106, 'Vidya', 'Kashyap', 103)

INSERT INTO RecurssiveCte VALUES (107, 'Rajesh', 'Kashyap', 103)

INSERT INTO RecurssiveCte VALUES (108, 'Shruthi', 'Kashyap', 105)

INSERT INTO RecurssiveCte VALUES (109, 'Sujay', 'Kashyap', 105)

INSERT INTO RecurssiveCte VALUES (110, 'Vijay', 'Kashyap', 106);

;WITH

  RecursiveCTE (Id, FirstName, SurName, MgrID, Lvl)

  AS

  (

    SELECT Id, FirstName, SurName, MgrID, 1 Lvl

    FROM RecurssiveCte

    WHERE MgrID IS NULL

    UNION ALL

    SELECT e.Id, e.FirstName, e.SurName, e.MgrID,r.Lvl + 1

    FROM RecurssiveCte e

    INNER JOIN RecursiveCTE r ON e.MgrID = r.Id

  )

SELECT FirstName + ' ' + SurName AS FullName,Lvl

FROM RecursiveCTE

ORDER BY Lvl, MgrID

I was going thr a particular requirement on MSDN forums today and i have shared the same here which could actually help in understanding CTE more


Declare @Budget Table
(
Budget_Revenue Int,
Budget_Traffic Int,
Actual_Revenue Int ,
Actual_Traffic Int
)
Insert @Budget Values
(0, 0, 2347585, 295),
(0, 0, 8796, 6),
(0, 0, 107772, 18),
(0, 0, 153940, 10),
(0, 0, 284510, 13),
(3772, 1, 2347585, 295),
(3772, 1, 8796, 6),
(3772, 1, 107772, 18),
(3772, 1, 153940, 10),
(3772, 1, 284510, 13),
(143355, 19, 2347585, 295),
(143355, 19, 8796, 6),
(143355, 19, 107772, 18),
(143355, 19, 153940, 10),
(143355, 19, 284510, 13),
(7545, 1, 2347585, 295),
(7545, 1, 8796 ,6),
(7545, 1, 107772 ,18),
(7545, 1, 153940 ,10),
(7545, 1, 284510 ,13),
(0, NULL, 2347585,295),
(0, NULL, 8796 ,6),
(0, NULL, 107772 ,18),
(0, NULL, 153940 ,10),
(0, NULL, 284510 ,13)

;With Cte As
(
Select Dense_Rank() Over (Order By (Select Case When ForSort = 295 then 0 Else ForSort End))Rnk,*
From (
Select T1.Budget_Revenue,T1.Budget_Traffic,Stuff(
(Select ','+ Convert(Varchar(10),T2.Actual_Revenue)
From @Budget T2
  Where T1.Budget_Revenue = T2.Budget_Revenue
And IsNull(T1.Budget_Traffic,1010) = IsNull(T2.Budget_Traffic,1010)
For Xml Path('')
),1,1,'') As Actual_Revenue,
 
Stuff(
(Select ','+ Convert(Varchar(10),T2.Actual_Traffic)
  From @Budget T2
     Where T1.Budget_Revenue = T2.Budget_Revenue
   And IsNull(T1.Budget_Traffic,1010) = IsNull(T2.Budget_Traffic,1010)
  For Xml Path('')
),1,1,'') As Actual_Traffic,T1.Actual_Traffic ForSort

From @Budget t1
) T1

),Budget_New As(Select Row_Number() Over (Order By (Select Null))RwID,Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic From Cte
Where Rnk=1
)
, Cte1 As
(
Select RwId,
       Budget_Revenue
       ,Budget_Traffic
       ,Actual_Revenue + ','Actual_RevenueCm
       ,Actual_Traffic + ',' Actual_TrafficCm
       ,Substring(Actual_Revenue,1,CharIndex(',',Actual_Revenue)-1) As Actual_Revenue
       ,Substring(Actual_Traffic,1,CharIndex(',',Actual_Traffic)-1) As Actual_Traffic
       ,CharIndex(',',Actual_Revenue) As ForActual_Revenue
       ,CharIndex(',',Actual_Traffic) As ForActual_Traffic
  From Budget_New
Where RwId = 1
Union All
Select
T1.RwId,
T1.Budget_Revenue
,T1.Budget_Traffic
,Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue))
        ,Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic))
        ,Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,CharIndex(',',Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)))-1)
        ,Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,CharIndex(',',Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic)))-1)
        ,CharIndex(',',Substring(T2.Actual_RevenueCm,ForActual_Revenue+1,Len(T1.Actual_Revenue)) ) As ForActual_Revenue
        ,CharIndex(',',Substring(T2.Actual_TrafficCm,ForActual_Traffic+1,Len(T1.Actual_Traffic)) ) As ForActual_Traffic
From Budget_New T1
Join Cte1 T2 On T1.RwId = T2.RwId+1
)
Select Budget_Revenue,Budget_Traffic,Actual_Revenue,Actual_Traffic From Cte1