CTE in SQL Server

What are CTE & why should we use them ?

CTE or Common Table Expressions are very similar to Views or Derived tables, They were first Introduced in SQL Server 2005.CTE can be used as a temporary result set which can then be referenced by a name and can be utilized as a Temporary / Virtual Table. Common Table Expressions can be used in conjunction with data modification operations.

CTEs can enhance code readability and can divide complex code into smaller blocks.CTE is just like Subqueries which can be called as if it was a table.

Unlike Derived Tables, CTE can be referenced multiple times without the need for code duplication.

CTEs are created and referenced on the go and don’t require a Physical existence in the database like a Table or a view. 

CTEs can be used for recursion and can be recursive up till 32 levels

CTE Syntax

Below is the basic syntax of a CTE.

WITH <CTE name> [ ( <column name> [,...n] ) ]
AS
( <query returning tabular data> )
<statement that use CTE>

Example of CTE

To go through the example of a CTE we will create a table EMPLOYEES and we will find out any Duplicate rows and will delete duplicate rows using CTE.

IF OBJECT_ID(N'EMPLOYEES', N'U') IS NOT NULL           
DROP TABLE EMPLOYEES;
CREATE TABLE EMPLOYEES
(ID INT,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT);
INSERT INTO EMPLOYEES VALUES
(1,'SHANE','RAY',5000),(2,'JACK','STRONG',7000),(3,'OLGER','PHILLIPS',9000);
select * from EMPLOYEES;

In the Below query, we have used ROW_NUMBER () function inside of a CTE to find out Duplicate rows.

WITH CTE AS
(
select ID,F_NAME,L_NAME,SALARY,ROW_NUMBER() 
OVER(PARTITION BY ID ORDER BY ID) RN from EMPLOYEES
)
SELECT * FROM CTE;

Once we find the duplicate rows we can use CTE to delete Duplicate rows.

WITH CTE AS
(
select ID,F_NAME,L_NAME,SALARY,ROW_NUMBER() 
OVER(PARTITION BY ID ORDER BY ID) RN from EMPLOYEES
)
DELETE FROM CTE WHERE RN > 1;

If we check the data in the Employees table you will observe that the duplicate record is now been deleted.

Recursive CTE

Below is an example of Recursive CTE which can be used to print numbers from 1 to 10.

with cte as
(
select 1 as n
union all
select n+1 from cte where n < 10
)
select * from cte

WITH MYCTE AS
( QUERY1 ) ,
MYNAME2 AS
( QUERY2 )
SELECT * FROM QUERY1 JOIN QUERY2;

IF OBJECT_ID(N'tempdb.dbo.##emp', N'U') IS NOT NULL           
DROP TABLE tempdb.dbo.##emp;
CREATE TABLE ##emp(EMP_ID int,f_NAME Varchar(20),Mgr_ID int);
INSERT INTO ##emp VALUES (100,'AJAY',100),(101,'RAJ',100),
(102,'SUMIT',103),(103,'JOOHI',102),(104,'KIMMI',103);
select * from tempdb.dbo.##emp;

WITH CTE AS
(SELECT * FROM ##emp) ,
 CTE1 AS
(SELECT * FROM ##emp)
SELECT * FROM CTE JOIN CTE1 ON CTE.EMP_ID=CTE1.Mgr_ID