Common Table Expressions in SQL Server

mcp111 8:01 pm on February 1, 2010 | Answers: 1    
Tags: common (36), cte sql query, expressions (3), server (37), sql (43), table (16)

Rate Knowledge: (1 votes)

 Loading ...

• mcp111 8:01 pm on February 1, 2010
  

  With the advent of SQL Server 2005, SQL Server got a new feature called Common Table Expressions ( CTE )

  Common Table Expression (CTE) is an alternative for using temporary tables or views.
  It is a temporary table result set whose scope is limited to the
  execution of a SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement.
  CTE is a virtual table which can be referenced several times in the associated query.
  Here is an example:

  USE AdventureWorks;

  WITH cteDirRpt(ManagerID, DirectReport) AS
  (
  SELECT ManagerID, COUNT(*)
  FROM HumanResources.Employee AS e
  GROUP BY ManagerID
  )
  SELECT Manager=Left(FirstName,1)+’. ‘
  +LastName, DirectReport
  FROM cteDirRpt dr
  JOIN HumanResources.Employee e
  ON dr.ManagerID = e.EmployeeID
  JOIN Person.Contact c
  ON c.ContactID = e.ContactID
  WHERE DirectReport > 5
  ORDER BY LastName

  This example displays the manager name and number of direct reports for all managers who have more than 5 direct reports. The CTE cteDirRpt first finds all the managers and their direct reports and then this CTE is joined with the employee table to get the actual names of the managers. Thus in one query we have efficiently got the data without having to use a temporary table.