Microsoft SQL Server 2008 R2 Unleashed- P177:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1704 CHAPTER 43 Transact-SQL Programming Guidelines Tips and Tricks 10 Clutch 3 2 16 Gear Box 3 2 5 Radiator 2 2 6 Intake Manifold 2 2 7 Exhaust Manifold 2 2 8 Carburetor 2 2 13 Piston 2 2 14 Crankshaft 2 2 In Listing the filter WHERE Ivl 2 in the recursive member is used as a recursion termination check recursion stops when Ivl 2. The filter on the outer query WHERE Ivl 2 is used to remove all parts up to the second level. Logically the filter in the outer query lvl 2 is sufficient by itself to return only the desired rows but for performance reasons you should include the filter in the recursive member to stop the recursion as soon as two levels below the drivetrain are returned. SQL Server allows the use of local variables in a CTE to help make the query more generic. For example you can use variables instead of constants for the part ID and level as shown in Listing . LISTING Using Local Variables in a Recursive CTE DECLARE @partid AS INT @lvl AS INT SET @partid 22 -- Car SET @lvl 2 -- two levels WITH PartsCTE partid partname parentpartid lvl AS SELECT partid partname parentpartid 0 FROM PARTS WHERE partid @partid UNION ALL SELECT 1 FROM Parts as P JOIN PartsCTE as PP ON WHERE lvl @lvl SELECT PartID Partname ParentPartid lvl FROM PartsCTE Go PartID Partname ParentPartid lvl 22 Car NULL 0 Common Table Expressions 1705 1 DriveTrain 22 1 23 Body 22 1 24 Frame 22 1 2 Engine 1 2 3 Transmission 1 2 4 Axle 1 2 12 Drive Shaft 1 2 You can also use recursive CTEs to perform aggregations such as counting the total number of subparts that make up each parent part as shown in Listing . LISTING Performing Aggregation with a Recursive CTE 43 WITH PartsCTE parentpartid Ivl AS SELECT parentpartid 0 FROM PARTS WHERE parentpartid is not null UNION ALL SELECT lvl 1 FROM Parts as P JOIN PartsCTE as PP ON WHERE is not null SELECT .
