- Common Table Expression (CTE)
CTE can be used as a temporary storage like temp tables,variables.A CTE is able provides a facility to refer itself.A recursive CTE is one which an intial CTE is executed repeatedly to return the subset of data until complete resultset is obtained.
- Structure of the CTE
A recursive CTE consists of three elements(As per MSDN):
- Invocation of the routine.The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
- Recursive invocation of the routine.The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
- Termination check.The termination check is implicit; recursion stops when no rows are returned from the previous invocation.
Example for CTE:
Consider a table containing EmployeeDetails with EmpID,ManagerID.EmployeeName,Designation.Here I want to get the result based on Manager Herirachy.Now we can create a table with the following T-SQL code.
CREATE TABLE [dbo].[EmployeeDetails]
[EmpID] [INT] IDENTITY(1, 1) NOT NULL,
[EmployeeName] [VARCHAR](50) NULL,
[ManagerID] [VARCHAR](50) NULL,
[Designation] [VARCHAR](50) NULL,
CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED ( [EmpID] ASC )WITH (
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]
We can insert some data into the table,here for example I am using an IT company Herirachy
INSERT INTO dbo.employeedetails
VALUES (‘E1’,NULL, ‘CheifTechnologyOfficer’),
(‘E3’, 2, ‘TeamLead’),
(‘E5’, 2, ‘DBA’),
I am issuing Select * from dbo.employeedetails
we can use CTE to return the hierarchical structure of company.just look into this.
WITH cte(empid, employeename, designation, managerid, [Level] )
–Defining Anchor Member
0 AS [Level]
WHERE managerid IS NULL
–Recursive Member Defenition
cte.[Level] + 1
FROM dbo.employeedetails AS e
INNER JOIN cte
ON e.managerid = cte.empid)
SELECT * FROM cte