Common Table Expression (a simple guide)

What is CTE?

Common Table Expression, this can be identified as a new feature which came up with Microsoft SQL Server 2005. We would be able to replace, creation of temporary tables and use of table variables with the use of CTE.

Comparison

When we do certain operations against data in a database, we may need to temporarily store data. Use of "Table Variables" is a better solution compared to the Temp Tables since the scope of a "Table Variable" will be limited only for that program. Data within a Table Variable will remain in the memory, till it reaches to a certain limit of size. Then after that it will be moved to temp database. But if we use the option of temp table, the temporary table will be there to accessed by any authenticated user. It will be there in the database, till we delete it.

But use of CTE can be identified as the best solution compared to the "Table Variables", since by this solution we would be able to imagine of a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Furthermore a CTE is a non-persistence view as well as a replacement for a view as well.

Syntax

With NameForTheTable (Names/Aliases of retrieved fields - Optional)

(Mandatory)

(

// your query goes here.........

)

SELECT * FROM NameForTheTable

Example

1. Simple example

WITH MyEmployee

AS
(
SELECT EmployeeID AS [EID], EmployeeName AS [EName], DepartmentID AS [DID], Salary AS [ESalary]
FROM Employee
)
SELECT * FROM MyEmployee

Here EID, EName, DID, ESalarywill be retrieved and stored as a temporary result set in the MyProduct.

2. Example with aliases

WITH MyEmployee (EID,EName, DID,ESalary)
AS
(
SELECT EmployeeID ,EmployeeName ,DepartmentID ,Salary
FROM Employee
)
SELECT * FROM MyEmployee

3. Using join

WITH Employee
AS
(
SELECT EmployeeID ,EmployeeName ,DepartmentID ,Salary
FROM Employee
),

WITH Department
AS
(
SELECT DepartmentID,DepartmentName
FROM Department
)
SELECT E.EmployeeID, E.EmployeeName, E.Salary FROM Employee E INNER JOIN Department D on E.DepartmentID=D.DepartmentID

4. Using Union

4.1

WITH AllEmployees AS
(

SELECT EmployeeID, LastName, DepartmentID
FROM TemporaryEmployee

UNION ALL

SELECT EmployeeID, LastName, DepartmentID
FROM PermanentEmployee

)
SELECT * FROM AllEmployees

4.2

WITH AllManagers AS
(

SELECT EmployeeID, LastName, ReportingTo
FROM Employees
WHERE ReportingTo IS NULL --this returns all the highest level managers

UNION ALL

SELECT e.employeeID,e.LastName, e.ReportingTo
FROM Employees e INNER JOIN Managers m
ON e.ReportingTo = m.employeeID - this returns all the below level managers
)
SELECT * FROM AllManagers

Finally

CTEs can be used to improve the performance of a stored procedure by replacing Temporay Tables and Table Variables. Once you get used to CTE, you will find how easily this can be applied in your complex stored procedures.

Please post your valuable comments.....

-Dilip Nilanka