Common Table Expression (CTE)

               few days ago I’ve written a  blog post in SQL server using CTE to find  Sundays. Lets dig some more deep in to this SQL server feature.

                                                       

    What is CTE

     The common table expression is a temporary named result set that can refer in a SELECT,INSERT, UPDATE or DELETE statement.

       simply a CTE is similar to a derived table but it is not stored as an object and lasts only for the duration of the query.

    Unlike a derived table, a CTE can be self referencing and can be referenced multiple times in the same query

we can use a CTE in View create statement also we can add CTE in to the new MERGE statement (In sql server 2008)

SQL Server support two types of CTEs

• recursive
• non recursive

 Syntax

A Common Table Expression contains three main parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

With CTE_Name (Column_Name_1, Column_Name_2 … Column_Name_n )
As
(
     cte_Query_Definition
)
--TSQL which uses the CTE

Some Examples

WITH Table1(Name,Designation,DateofJoin)
As   
(
    select CN.Name,D.Name,E.DateOfJoin from ContactName CN
    Inner join AM_Employee E on E.ContactId = CN.ContactId
    Inner join AM_Designation D on E.DesignationId = D.Id
)
select Name,Designation,DateofJoin from Table1