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] ) ]
( CTE_query_definition )
The statement to run the CTE is:
We can call the CTE as we call a normal table from SELECT statements.
WITH salesCTE (SperID, NetSales)
SELECT SperID, ROUND(SUM(SubTotal), 2)
WHERE SperID IS NOT NULL
GROUP BY SperID
SELECT sp.FirstName + ' ' + sp.LastName AS FullName,
sp.City + ', ' + StateProvinceName AS Location,
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
cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
WHERE ManagerID IS NULL
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
WHERE EmployeeID = cteReports.MgrID) AS Manager
ORDER BY EmpLevel, MgrID
Hope this is interesting.
Posted by: MR. JOYDEEP DAS