You are a database developer. You plan to design a database solution by using SQL Server 2008. The database will contain a table that has a parent-child relationship to itself. Each child might also be a parent. This might exist up to 10 levels deep. You need to retrieve all levels by using a single Transact-SQL query.
What should you do?
A.
Write a query to return the first level, and then add a correlated subquery to get the remaining levels.
B.
Write a query to return the first level, and then use the CROSS JOIN operator to join the table back to itself to get the remaining levels.
C.
Create a common-table expression to return the first level and then union back to itself to get the remaining levels.
D.
Create a view that returns the first level, and then use the FULL OUTER JOIN operator to join the table back to the view to get the remaining levels.
Explanation:
A common table expression (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.