CTE

 

A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statements.

Form SQL Server version 2005 CTE is introduced.

A CTE can use used as to create a recursive query or as substitute of a view, where view is not required. Enable grouping by a column that is derived from a scalar sub select, or a function that is either not deterministic or has external access.  

The CTE improves the readability of a complex query by dividing query into different logical blogs.

In addition CTE in SQL Server 2008, we can add the new MARGE function on it.

 

The syntax is mentioned bellow:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

 

The statement to run the CTE is:

 

SELECT <column_list>

FROM expression_name;

 

 

We can call the CTE as we call a normal table from SELECT statements.

Example:

WITH salesCTE (SperID, NetSales)

  AS

  (

    SELECT SperID, ROUND(SUM(SubTotal), 2)

    FROM   Sales.sale_OrdHdr

    WHERE  SperID IS NOT NULL

    GROUP BY SperID

  )

 

SELECT sp.FirstName + ' ' + sp.LastName AS FullName,

       sp.City + ', ' + StateProvinceName AS Location,

       ts.NetSales

FROM   Sales.sPerson AS sp

       INNER JOIN salesCTE AS ts ON sp.BusinessEntityID = ts.SperID

ORDER BY ts.NetSales DESC

 

As we told that CTE can be recursive, the bellow example give us a outline of that

WITH

  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)

  AS

  (

    SELECT EmployeeID, FirstName, LastName, ManagerID, 1

    FROM   Employees

    WHERE  ManagerID IS NULL

    UNION ALL

    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,

           r.EmpLevel + 1

    FROM   Employees e

           INNER JOIN cteReports r ON e.ManagerID = r.EmpID

  )

 

SELECT FirstName + ' ' + LastName AS FullName,

       EmpLevel, (SELECT FirstName + ' ' + LastName

                  FROM   Employees

                  WHERE  EmployeeID = cteReports.MgrID) AS Manager

FROM   cteReports

ORDER BY EmpLevel, MgrID

 

Hope this is interesting.

 

Posted by: MR. JOYDEEP DAS