THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

Recursive CTEs in AdventureWorks 2008

The advent of HierarchyID removed the adjaceny pairs pattern (MangerID) from the employee table in AdventureWorks 2008. The recursive CTE example in BOL still uses the old AdventureWorks structure, so the code won't run in AdventureWorks 2008. I know that recursive CTE's are yesterday's news, but they're still useful as far as I'm concerned. So, if you want to learn about them and play with them in AdventureWorks 2008, here's some code to make your life easier...

(note: The new AdventureWorks 2008 does not include the adjacency pairs ManagerID column, the contacts table is changed to Person table, and the ContactID is changed to BusinessEntityID.)

-- Recreate Adjacency Pairs Pattern

ALTER TABLE HumanResources.Employee

 Add ManagerID INT;




-- Populate the new ManagerID Column using join condition to match



  SET ManagerID = M.BusinessEntityID

  FROM HumanResources.Employee E

    JOIN HumanResources.Employee M

      ON M.OrganizationNode = E.OrganizationNode.GetAncestor(1);



 -- This query demos the recursive CTE


WITH OrgPath (BusinessEntityID, ManagerID, lv) 

AS (

   -- Anchor

      SELECT BusinessEntityID, ManagerID, 1

        FROM HumanResources.Employee

        WHERE ManagerID IS NULL -- should only be EmployeeID 1

        -- WHERE EmployeeID = 1 -- the CEO


    -- Recursive Call


      SELECT E.BusinessEntityID, E.ManagerID, lv + 1

        FROM HumanResources.Employee E

          JOIN OrgPath

            ON E.ManagerID = OrgPath.BusinessEntityID


SELECT Emp.BusinessEntityID, Emp.JobTitle,

    C.FirstName + ' ' + C.LastName AS [Name],

    M.FirstName + ' ' + M.LastName AS [Manager], Lv

  FROM HumanResources.Employee Emp

    JOIN OrgPath

      ON Emp.BusinessEntityID = OrgPath.BusinessEntityID

    JOIN Person.Person AS C

      ON C.BusinessEntityID = Emp.BusinessEntityID

    Left Join Person.Person AS M

      ON Emp.ManagerID = M.BusinessEntityID



Published Friday, October 3, 2008 5:09 PM by Paul Nielsen


No Comments
New Comments to this post are disabled

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement