Hello world, l hope everyone is having peace where ever you are. This week has been a tough one for the United States, I’m talking in regards of the Theater shooting in Colorado. It’s the craziest thing to happen and I extend my my prayers and condolence to the bereaved families and prayers to those in the hospital recovery. Lets pray for each other.
Ok, today’s discussion is on a CTE. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT,INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. If you don’t remember derived table, please go back to the earlier tutorial on derived table and go through, it’s very easy to understand.
A CTE can be used to Create a recursive query, Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. Reference the resulting table multiple times in the same statement. Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
Once again, l’m mentioning all these stuff that l’ve covered in the earlier tutorials so if you don’t understand any of the database objects, you can simply go into the database category and see all the topics regarding these objects. Once again, they are very easy to understand. Now lets look at the structure of a CTE.
A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined,it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.
The basic syntax structure for a CTE is:
WITH <CTE_Name> [ col1,col2,col3,…..coln ]
AS
( CTE_query_definition )
After defining the CTE, you can run the CTE is like this,
SELECT <column_list>
FROM CTE_name;
This looks just like a view and how to access the result of a view. so it’s easy if you already understand a view. The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition. The following example shows the components of the CTE structure: expression name, column list, and query. Lets create a CTE using Adventureworks SalesOrderHeader table and retrieve SalesPersonID,SalesOrderID and SalesYear.
USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
Now, after defining this CTE, we can reference it outside, but remember this must be batch bound.
-Lets define the outer query referencing the CTE above.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear
–So what we are doing in this query is to display all the salesPersonIDs and the total sales made by that salesPerson in each year.
Note the CTE only give us all salesPerson, SalesOrderID and the SalesYears but we are able to do aggregate function count for the salesorderID for each year, by grouping the sales year, and salespersonid.
Now things can get a little messing where we recursively call the CTE. A (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained. A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example, displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may,
in turn, have subcomponents or may be components of other parents. A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. Lets see the Structure of a Recursive CTE.
A recursive CTE consists of three elements:
-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. I have discussed SET operators in earlier Tutorial so once again, if something looks confusing here, it’s good to go over those operators. Now, talking about the Invocation of the routine, 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.
NB. An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. When testing the results of a recursive query, you can limit the number of recursion levels allowed for a specific statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement.
Ok, that’s lot of information, lets see how the real thing is implemented.
WITH cte_name ( column_name [,…n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
After defining it, you can see the outcome just by running it normall
SELECT *
FROM cte_name
The semantics of the recursive execution is as follows:
1. Split the CTE expression into anchor and recursive members.
2.Run the anchor member(s) creating the first invocation or base result set (T0).
3. Run the recursive member(s) with Ti as an input and Ti+1 as an output.
4. Repeat step 3 until an empty set is returned.
5. Return the result set. This is a UNION ALL of T0 to Tn.
Lets do an example using adventures. The following example shows the semantics of the recursive CTE structure by returning a hierarchical list of employees, starting with the highest ranking employee in the Adventure Works Cycles company. A walkthrough of the code execution follows the example.
— 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)
);
— Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N’Ken’, N’Sánchez’, N’Chief Executive Officer’,16,NULL)
,(273, N’Brian’, N’Welcker’, N’Vice President of Sales’,3,1)
,(274, N’Stephen’, N’Jiang’, N’North American Sales Manager’,3,273)
,(275, N’Michael’, N’Blythe’, N’Sales Representative’,3,274)
,(276, N’Linda’, N’Mitchell’, N’Sales Representative’,3,274)
,(285, N’Syed’, N’Abbas’, N’Pacific Sales Manager’,3,273)
,(286, N’Lynn’, N’Tsoflias’, N’Sales Representative’,3,285)
,(16, N’David’,N’Bradley’, N’Marketing Manager’, 4, 273)
,(23, N’Mary’, N’Gibson’, N’Marketing Specialist’, 4, 16);
USE AdventureWorks2008R2;
GO
—Create Recursive CTE
WITH DirectReports_CTE (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
— Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 0 AS Level
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
— Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM dbo.MyEmployees AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
INNER JOIN DirectReports_CTE AS d
ON e.ManagerID = d.EmployeeID
)
— Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports_CTE d
INNER JOIN HumanResources.Department AS dp
ON d.DeptID = dp.DepartmentID
WHERE dp.GroupName = N’Sales and Marketing’ OR Level = 0;
This is cool but can send you thinking huh, what is going on? Reading over it at least 3 times can get you familiar with CTE, its a good trick to get things done easily if you know it. I hope this helps and l’m going to work on Pivot and Unpivot tables in the next tutorial. Peace. Corrections, suggestion and feedback is welcome.
