What is Common Table Expression(CTE) in SQL Server with an example?

Common Table Expression(CTE)

1. Common Table Expression is the temporary result set, and it should be with in the execution of the one SELECT, DELETE, UPDATE, INSERT or Create View statement. 
2. It will work during query execution only. 
3. This will store as an object in the SQL Server. 
4. CTE is the self referencing and could be able to refer multiple time in the same query. 

Syntax:

WITH Expression_Name [ ( ColumnName [1,...n] ) ]
AS
( CTE query definition )

Usage of CTE:

• We can create a recursive query. 
• CTE will be the substitute of VIEW. no need to store definition in the metadata. 
• This is enable grouping by a column CTE will derived from the scalar subselect. 
• Need to refer the same table multiple times in the same query. 
• Readability will increase and maintenance of the complex queries very easily. 
• This one of the best SQL Server Interview Question.

Examples of CTE:

Before going to run below queries please run input table script.

--example one
with EmployeeDept_CTE as(
select e.eno,e.ename,ed.dno from employeeMaster e full outer join empDeptMapping ed on e.eno=ed.eno)
select * from EmployeeDept_CTE

--example two
with EmployeeDept_CTE([Employee Number],[Employee Name],[Dept No]) as(
select e.eno,e.ename,ed.dno from employeeMaster e full outer join empDeptMapping ed on e.eno=ed.eno)
select * from EmployeeDept_CTE

WE have to use the CTE name quickly, after defining the CTE.  like
In this example, we have to use below line after define EmployeeDept_CTE
select * from EmployeeDept_CTE other wise we will get below error. 

Msg 208, Level 16, State 1, Line 7
Invalid object name 'EmployeeDept_CTE'.

In the above example one we have used EmployeeDept_CTE with out declaring column  name. 
In the above example two we have used EmployeeDept_CTE with declaring column name. 
Both the output will same, but in the output column names will display in the second query. 


Output for second query:

Employee Number	Employee Name	Dept No
1	ranga	1
2	swati	1
3	anil	1
4	john	3
5	Kavitha	2
6	padma	4