What is CTE in SQL Server

 

Common Table Expressions

 

Common Table Expressions are also called CTEs. This feature was introduced with SQL Server 2005. The CTE is preferred to use as an alternative to a Subquery/View.

 

Sub-queries

 A sub-query is a query within a query. It is also called an inner query or a nested query. A sub-query is usually added in a where clause of the SQL statement.

Select Name,Age, employeeID    

From employee    

Where employeeID in    

(   

 Select employeeID from salary where salary >=1000 

) 

Why to use a CTE

 

In SQL, we will use sub-queries to join the records or filter the records from a sub-query. Whenever we refer the same data or join the same set of records using a sub-query, the code maintainability will be difficult. A CTE makes improved readability and maintenance easier.

Syntax

With CTEName (column1, column1,….)   

AS   

(Query)  

 

We can use another CTE within a CTE but the query using the CTE must be the first query appearing after the CTE.

Example 

With salaryCTE      --cte1

AS  

(Select employeeID from salary where salary >=1000

)    

, EmpDetailsCTE      --cte2

AS    

(   

Select Name,Age, employeeID  

From employee Emp Join salaryCTE sa  

on Emp. employeeID = sa. EmployeeID)  

In the preceding example, first we defined the CTE called salaryCTE. The SalaryCTE has a subquery that will select the employeeID from the salary table for the employee's salary greater than or equal to 1000. Then we have created one more CTE called EmpDetailsCTE that will select the name, age and employeeID from the employee table for the employeeID's of salaryCTE.

 

Advantages of CTE

·        CTE improves the code readability.

·        CTE provides recursive programming.

·        CTE makes code maintainability easier.

·        Though it provides similar functionality as a view, it will not store the definition in metadata.