Title: SQL Server-XML-Lesson 6-Using MAXRECURSION with Common Table Expressions (CTE)
Duration: 16 minutes
Summary: In this video, we are discussing CTE (Common Table Expression), and how we can use the MAXRECURSION option within a CTE. You can think of a CTE as a temp result set that is defined within the execution scope of a single select, insert, update, or delete statement. It lasts only for the duration of the query. CTE has a great advantage in that it is able to reference itself (recursive CTE). To illustrate the use of CTEs, we create a Personnel table with employees and managers. Then we create the CTE, which is composed of two sections: anchor set and recursive set (joined by 'union all'). In the recursive set is where the CTE references itself. At the end of the CTE we add the option (MAXRECURSION) to limit the recursion to 2 levels.
Methodology of the development of example: Good coding standard and simplified design to illustrate the key points.
Technology Used: SQL Server 2008
Keywords: XML, table, database, primary key, Common Table Expression (CTE), MAXRECURSION, union all.