CTE in Sql Server  

CTE was introduced with the SQL Server 2005. A CTE (Common Table Expression) is temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statements of sql server.

0 473 Technology | Sql Server
Tue Dec 25 , 2018

Common Table Expressions are defined within the statement using the WITH operator. You can define one or more CTE's. They always returns a result set. They are used to simplified complex queries, i.e, you could eliminate a derived table from the main query body.


Syntax

With derivedtablename (column1,column2,….)  
  
AS  
  
(Query)  

Example

With SalaryCTE(EmployeeNo)  
  
AS  
  
(Select EmployeeID from Emp_Salary where Salary >=5000),

EmpDetailsCTE( Name, EmployeeID ,Salary)  
  
AS  
  
(Select Name, EmployeeID  
  
From Employee emp Join SalaryCTE sa  
  
on emp. EmployeeID = sa. EmployeeID) 

Need of CTE's

  • CTE promotes 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.
  • CTE promotes ranking function such as ROW_NUMBER(), RANK(), etc.


Subscribe & Get Regular Updates On Your E-mail