You are a database developer on an instance of SQL Server 2008. You maintain a Research database that contains the following Category table:
The Category table contains a hierarchy of living things to the sub-species level. Each row in the Category table has a ParentCatID value that corresponds to the item’s parent classification in the hierarchy. The parent-child relationship represented by the CatID and ParentCatID columns is nested more than 20 levels. A sample of data in the Category is shown as follows:
You want to query the Category table and return a list of all living things, but only down to 5 levels of nesting. You want to accomplish this with a single query. You want to minimize the complexity of the query, but provide optimal performance.
Which action should you take?
A. Create a query that contains four subqueries.
B. Create a recursive CTE.
C. Create a query that uses the CROSS APPLY clause.
D. Create a query that returns a calculated hierarchyid value.
Correct Answer: B
Explanation/Reference:
A Common Table Expression (CTE) can be used to make Transact-SQL code more readable when a query needs to reference the same result set multiple times.
You can define a CTE by including the WITH clause with a query. When defining a CTE, the WITH clause specifies the expression name that will be used in the statement that immediately follows the CTE definition. The statement immediately following the CTE definition can use the expression one or more times, as if it were a table or view. The WITH clause must contain a column list identifying the available columns, unless all columns in the expression’s query have distinct names. The syntax for creating a CTE is as follows:
WITH expression_name [(column_name [,…n])]AS (CTE_query_definition)
After you create the CTE, the statement immediately following the CTE definition can reference the expression, but only the columns defined in the CTE expression are accessible. CTEs can be used as an alternative to a view, temporary table, or subquery. The query for a CTE definition can reference the original CTE. In this scenario, you could use the following query:
;WITH CatCTE AS
(
SELECT CatID, Name, ParentCatID, 0 As CatLevel
FROM Category
WHERE ParentCatID = 0
UNION ALL
SELECT p.CatID, p.Name, p.ParentCatID, c.CatLevel + 1
FROM Category p
INNER JOIN CatCTE c ON p.ParentCatID = c.CatID
)
SELECT * FROM CatCTE WHERE CatLevel < 5;
This 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 row at the top of the hierarchy, which has a ParentCatID value of 0. This CTE 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. This statement uses the UNION ALL operator to combine the results of the first and second SELECT statements. You can also use the UNION, INTERSECT, or EXCEPT operators, but the UNION ALL operator must always be specified after the last anchor member before recursive members. Because set operators are used, both SELECT statements must contain the same number of columns, and 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 item with its respective parent, continuing top-down through the hierarchy.
You should not create a query that contains four subqueries. This would add complexity to the query, and would not optimize performance.
You should not create a query that uses the CROSS APPLY clause. The APPLY clause is used in the FROM clause of a query to join a table to a table-valued function. The table-valued function is called for each row returned by the outer query. The APPLY clause allows you to easily call a table-valued function for each row returned by a query. The OUTER APPLY clause returns all rows from the outer query, even if the row does not return a value for the table-valued function. The CROSS APPLY clause returns only the outer query rows for which the table-valued function returns a value.
You should not create a query that returns a calculated hierarchyid value. The hierarchyid data type is a special variable-length, CLR-supplied data type that can be used to represent hierarchical data. Defining a hierarchyid data type column allows you to use various system-supplied methods, such as GetRoot, GetDescendant, GetLevel, Read, and Write, which allow you to perform tasks on the hierarchy. Each value stored in the table is an internal binary value that represents the row’s position in the hierarchy. In this scenario, you could represent the relationship by implementing a hierarchyid column in the table, but you would have to modify the table structure.
