What Is CTE In SQL Server With Example

Introduction

In this article we will learn about what is CTE and why do we need to use CTE in sql server. Difference between CTE and temp table and example of CTE.


Previous Updates

In previous articles we have learnt Difference between  Scope_Identity(), @@Identity and Ident_Current . Stuff and Replace in SQl. Temp table and table variable .Group By in SQL Server and its use. If Else and Case statement  , While Loop . What is Cursor in sql and use of cursor. Difference between Row_Number(), Rank(), Rank_Density() with example.

What Is CTE

 CTE is stands for Common Table Expression. It was firstly introduced with SQL Server 2005. CTE is work as a temporary result set just like Asp.net dataset which is defined with in the scope of Insert, Update ,Delete ,Select statement.
    In other words we can say that CTE is used to store data temporarily and at the same time we can perform insert, Delete, Update and Select operation onto that. It is mainly used in recursive queries.

CTE Syntax

WITH  TempCTE (Column1, Column2, Column3) AS (     SELECT Column1, Column2, Column3     FROM  YourTable ) SELECT * FROM TempCTE  // Select operation SELECT * FROM TempCTE Where Column2 < 100  // Get filtered data

Why Do We Need CTE

When you work with sub-queries than always you need to select the part of the data returned by subqueries like join data from multiple tables . In this situation you need to write alias for better understanding or fetch the records directly. But what happend next if this query become more complex after few new requirements changed (new table added on Join condition). Your query will be unmaintainable and not readable to anyone.
By using CTE you can define your sub-queries at once and select all the returned data from CTE alias using simple select clause same like your normal tables.

With the help of CTE you can perform any filtration on your dynamic result set. 
For example in a scenario if we are not using CTE and performing direct filtration on selected record then our query is less readable and become complex . See below example 

SELECT * FROM  (         SELECT ED.Address, E.Name, E.Salary From Emp_Detail  ED         Inner JOIN Employee E on E.EID = ED.EID) T WHERE T.Salary > 10000 ORDER BY T.NAME

Now we perform the same operation using CTE which is more understandable and easy to grape for anyone.

With TempCTE (Address, Name, Salary)        -- Temporary table Column names AS ( SELECT ED.Address, E.Name, E.Salary From Emp_Detail  ED         Inner JOIN Employee E ON E.EID = ED.EID ) -- Perform operation on CTE data SELECT * FROM  TempCTE WHERE T.Salary > 50000 ORDER BY T.NAME

When To Use CTE

Common table expression gives the same functionality just like view. You can write recursive query using CTE. When we have complex structure in joining and need to divide the complexity into separate simple logical building blocks then CTE is best to work with.