You are a database developer on an instance of SQL Server 2008. Your Personnel table is defined as follows:
The Personnel table has a primary key defined on the EmployeeID column and contains a table-level CHECK constraint that ensures that only the employee with an EmployeeID value of 1 may have a NULL value for the ManagerID column.
You execute the following Transact-SQL:
;WITH MyCTE
AS
(
SELECT EmployeeID, ManagerID, HireDate
FROM Personnel
WHERE ManagerID IS NULL
UNION ALL
SELECT p.EmployeeID, p.ManagerID, p.HireDate
FROM Personnel p INNER JOIN MyCTE m ON p.ManagerID = m.EmployeeID
)
SELECT * FROM MyCTEOPTION (MAXRECURSION 3);
What is the result?
A. The statement executes successfully with no errors and displays only three rows.
B. The statement fails because the CTE has not been defined as recursive.
C. The statement executes successfully with no errors and displays all employees, including each employee’s ManagerID and HireDate.
D. The statement generates an error message, but displays employees that are at the first four levels of the organization chart.
Correct Answer: D
Explanation/Reference:
The query for a CTE definition can reference the original CTE. For example, in this scenario, the CTE definition references itself on the right side of the INNER JOIN. This causes the CTE to be recursive. The first SELECT statement in the CTE definition returns the employee with an EmployeeID value of 1, who is at the top of the organization chart because this employee does not have a manager. This query is referred to as an anchor member because it does not reference the CTE definition. The SELECT statement following the UNION ALL operator is referred to as a recursive member because it references the CTE definition. When creating a recursive CTE, forward referencing is not allowed. This means that the anchor member must be specified first.
In this scenario, you included the UNION ALL operator to combine the result of the first SELECT statement with the result of the second SELECT statement. You can also use the UNION, INTERSECT, or EXCEPT operator, but the UNION ALL operator must always be specified after the last anchor member before recursive members. Because these set operators are used, both of the SELECT statements must contain the same number of columns, and the columns must have the same data type. In this scenario, the SELECT statement following the UNION ALL operator joins the original table with the result of the CTE. This joins each employee with the associated manager, continuing top-down through the organization chart with each recursive call.
In the statement that follows the CTE expression definition, you use the MAXRECURSION hint to limit the level of recursion to three levels. This means that the recursive query can only call itself three times. This traverses four levels of the organization chart: one for the top-level employee, and one for each recursion. In this scenario, when the recursion level maximum is exceeded, the result set up to that point is returned, but the statement terminates with a message similar to the following:
Msg 530, Level 16, State 1, Line 3The statement terminated. The maximum recursion 3 has been exhausted before statement completion.
You can use the MAXRECURSION hint to return only specific rows or prevent an infinite loop in a recursive CTE. If you do not specify a MAXRECURSION hint, recursion is limited to 100 levels by default. You can remove all maximum recursion restrictions by setting the MAXRECURSION hint to 0.
In addition, the recursive member of a CTE definition cannot contain certain constructs, such as the DISTINCT or TOP keywords, a GROUP BY or HAVING clause, or a reference to a subquery or aggregate function. Also, if a CTE is included in the definition of a view, the view is non-updateable.
The statement does not execute successfully with no errors and display only three rows. The statement might generate only three rows, but it could also display more than three rows, depending on the number of employees and organizational levels represented in the rows of the Personnel table. If the number of levels exceeded four, the statement would also generate an error message indicating the maximum recursion had been exceeded.
The statement does not execute successfully with no errors and display all employees, including each employee’s ManagerID and HireDate. In this scenario, you specified a MAXRECURSION hint for the CTE. Selecting from the CTE may or may not display all employees, depending on the number of employees and organizational levels represented in the Personnel table. In addition, an error would be displayed if the maximum recursion were exceeded.
