CTE - Common Table Expression In Sql Server

Applies To SQL Server 2005+

CTE (Common Table Expression) is very useful construct in sql server and very exciting feature added to sql server. CTE is temporary result set which is not available as data in a table or view. prior to CTE there are various scenario where we used complex join, temp Table and cursor.  Here we will how see CTE is helpful over temptable or Cursor.

Syntax Of CTE

;With <CTEName> (Optional ColumnList)
As
(
Select * From TableName
)
Select * From CTE

Let’s Do Some Task With Help Of CTE

Taks 1 : List All The Dates Of Current Month

With CTE
As
(
    Select GETDATE()-DAY(Getdate())+1 As DateList
    Union All
    Select DateList+1 From CTE 
    Where DateList<DateAdd(m,1,GETDATE())-DAY(Getdate())
)
Select CONVERT(Date,DateList) As DateList from CTE

Output : This is recursive use of CTE

Task 2 : Delete Duplicate Records From The Table

Create Table With Following Script

Create Table Orders(OrderNumber Varchar(20),OrderPrice Decimal(20,2))
Go
Insert Into Orders Values
('TR0001',56.70),('TR0002',65.00),('TR0003',35.50),
('TR0002',65.00),('TR0001',56.70),('TR0001',56.70),
('TR0004',36.30),('TR0005',90.20),('TR0003',35.50),
('TR0004',36.30),('TR0006',90.20),('TR0007',35.50)
Go
Select * From Orders 

So as you can see we have many duplicate OrderNumbers.

Now Let’s See how can we delelete Duplicate records using CTE.

;With DeleteDuplicate
As
(
    Select *,ROW_NUMBER() Over (Partition By OrderNumber,OrderPrice 
Order By OrderNumber) As RowNo
    From Orders
)
Select * From DeleteDuplicate

As You can see the out of above query, Row No Is Given To Every OrderNumber, Each Duplicate OrderNumber Has RowNo in Series. Now Next Is to Delete The Duplicate Rows Found. I can simply say delete All Rows Where RowNo>1. Let’s See How ?

Delete the Data Now

;With DeleteDuplicate
As
(
    Select *,ROW_NUMBER() Over (Partition By OrderNumber,OrderPrice Order By OrderNumber) As RowNo
    From Orders
)
Delete From DeleteDuplicate Where RowNo>1
 

Now lets see what we have in table now

Select * From Orders 
Order By OrderNumber

Task 3 : Month Wise Sale Or Data

Create Table With Following Script

Create Table Sale(SaleId  Int Identity(1,1),SaleDate DateTime,BillAmount Decimal(22,2))
Go
Insert Into Sale Values
('2011-01-07',3457.00),('2011-01-12',876.00),('2011-01-27',2346.00),
('2011-04-01',987.00),('2011-04-13',564.00),('2011-04-23',7865.00),
('2011-08-09',11092.00),('2011-08-12',1243.00),('2011-08-20',6657.00),
('2011-09-04',456.00),('2011-09-16',876.00),('2011-09-21',8876.00)
Go

To Achieve Above Task We Generally Write following Query

Select DateName(Month,SaleDate) MonthName,SUM(BillAmount) As TotalSaleAmount
From Sale
Group By Month(SaleDate),DateName(Month,SaleDate)
Order By Month(SaleDate)

as you can see the output below shows only those month where sale happened

MonthName	TotalSaleAmount
April	9416.00
August	18992.00
January	6679.00
September	10208.00

What if the requirement is to get all the month name irrespective of sale.

Let see use of CTE in this scenario

;With CTE
As
(
    Select DATENAME(Month,'1900-01-01') As MonthName,
    DateAdd(M,1,'1900-01-01') As MonthDate,1 As MonthValue
    Union All
    Select DATENAME(Month,MonthDate) As MonthName,
    DATEADD(M,1,MonthDate) As MonthDate,MonthValue+1 as MonthValue
    From CTE Where Year(MonthDate)<=1900
)
Select MonthName,Isnull(SUM(BillAmount),0) As TotalSale
From CTE
Left Join Sale On Cte.MonthValue=Month(SaleDate)
Group By MonthValue,MonthName
Order By MonthValue

Output :

MonthName	TotalSaleAmount
January	6679.00
February	0.00
March	0.00
April	9416.00
May	0.00
June	0.00
July	0.00
August	18992.00
September	10208.00
October	0.00
November	0.00
December	0.00

You can see above the desired result with the help of CTE.

Hope From Above Examples You Might have got Information and Functionality Of CTE, And Now You Can Utilize CTE in Different Scenario According to Requirement.