CTE or Common Table Expressions, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table and can be declared once and referenced multiple times in a query. One major advantage over derived table is, CTEs can be recursively defined and so it eliminates the need for recursive stored procedures.
In general, we use CTE for data selection only. But a CTE can be used for INSERT, UPDATE, DELETE and MERGE operations as well. Like derived table, a CTE can be used in functions, stored procedures, triggers, or views. In other words, CTE is temporary named result set of a query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement.
The general syntax of a CTE is:
[ WITH <common_table_expression> [ ,...n ] ]
expression_name [ ( column_name [ ,...n ] ) ]
( CTE_query_definition )
I will write more on using CTE but for today, have a look into a recursive CTE to get employee level of employee from employee table.
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
;WITH DirectReports( EmployeeID, Title, EmployeeLevel) AS
SELECT EmployeeID, Title, 0 AS EmployeeLevel
WHERE ManagerID IS NULL
SELECT e.EmployeeID, e.Title, EmployeeLevel + 1
FROM dbo.MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
SELECT EmployeeID, Title, EmployeeLevel
ORDER BY EmployeeLevel;