Common Table Expression (CTE) in SQL 2005/2008/2012

Good evening friends..Today we are going to learn about Common table expression that is a new feature which was introduced in SQL server 2005  and available in later versions as well.

Common table Expression :- Common table expression can be defined as a temporary result set or in other words its a substitute of views in SQL Server. Common table expression is only valid in the batch of statement where it was defined and cannot be used in other sessions.

Syntax of declaring CTE(Common table expression) :-

with [Name of CTE]
as
(
Body of common table expression
)

Lets take an example :-

CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

I have created two tables employee and Dept and inserted 5 rows in each table. Now I would like to join these tables and create a temporary result set to use it further.

With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

Lets take each line of the statement one by one and understand. 

To define CTE we write "with" clause, then we give a name to the table expression, here I have given name as "CTE_Example"

Then we write "As" and enclose our code in two brackets (---),  we can join multiple tables in the enclosed brackets.

In the last line, I have used "Select * from CTE_Example" , we are referring the Common table expression in the last line of code, So we can say that Its like a view, where we are defining and using the view in a single batch and CTE is not stored in the database as a permanent object. But it behaves like a view. we can perform delete and update statement on CTE and that will have direct impact on the referenced table those are being used in CTE. Lets take an example to understand this fact.

With CTE_Example(EID,DeptName)

as

(

select EID,DeptName from DEPT 

)

delete from CTE_Example where EID=10 and DeptName ='Payroll'

In the above statement we are deleting a row from CTE_Example and it will delete the data from the referenced table "DEPT" that is being used in the CTE.

I hope this article will be helpful to you and you will be able to use CTE whenever you find it suitable.