SQL Server (CTE)

 

 

 

 

What is CTE

 

CTE stands with Common Table Expression. CTE can be thought as a temporary result set that defined within the execution scope of Single Select/Update/Delete/Create View.

 

This worked like derived table which does not store data as an object and last only till the duration of query.

 

Where Developer can use CTE

 

1) When Developer wants to use Recursive Query

2) Substitute of View when you want to run the View and do not want to store the definition

3) Refrence as a table which developer uses very often

 

 

Disadvantage of CTE

 

CTE can not be nested like Sub Query

 

How to use

 

USE <DB NAME>

GO

 

With TEST_CTE (Emp_Id,Emp_Name,DOJ)

as

--Define the Query

(

 

Select Emp_Id,Emp_Name,DOJ  from <Table Name>

 

)

 

--Define the Query Result

 

 

Select Emp_Id,Emp_Name,DOJ from Test_CTE

 

 

 

Happy Reading!!!!!!!!!!!!!!

mesanjaybakshi@gmail.com