locked
Implementing Hierarchies in SQL Server RRS feed

  • Question

  • I want to be able to implement infinite levels of Hierarchies in SQL Server (2012), in addition to being able to address issues like same child having more than one parent (eg. An Employee could end up having 2 different managers - eg. Project Manager,  Delivery Manager).

    One way is to have self referencing table (where each row has a parent id , referencing to a parent record - but this would not work in cases where a child  has more than 1 parent).

    Are there other more efficient ways?

    What is the best way to implement this?

    Thanks,

    P

    Friday, June 12, 2015 1:34 PM

Answers

  • "Nested Set" method by Joe Celko seems like a very simple solution (with each row having a left and right value)

    Only challenge could be managing Nodes (Ie deleting , updating, inserting new nodes etc.)

    • Marked as answer by SQLDev555 Monday, June 22, 2015 6:21 AM
    Monday, June 22, 2015 6:21 AM

All replies

  • Another way is to use hierarchyid data type

    see

    https://msdn.microsoft.com/en-us/library/bb677213.aspx


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 12, 2015 1:50 PM
  • Isn't there another way to do it ? Apart from using HierarchyId ?
    Thursday, June 18, 2015 2:00 PM
  • If so, I would have simple parent /child table  to allow the children having more than one parent. You can put a PK on parentid,childid combination

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, June 19, 2015 3:40 AM
  • Hi , Thanks.

    You mean like have the root nodes in a parent , and  and the children in child table ?

    I would also like to maintain infinite levels of heirarchy.

    eg.  project manager --> technical lead --> team lead --> Developer   etc....

    Friday, June 19, 2015 3:59 AM
  • Well, see Itzik's hierarchy examples , sure you need a little bit modify it for your needs

    CREATE TABLE Employees
    (
      empid   int         NOT NULL,
      mgrid   int         NULL,
      empname varchar(25) NOT NULL,
      salary  money       NOT NULL,
      CONSTRAINT PK_Employees PRIMARY KEY(empid),
      CONSTRAINT FK_Employees_mgrid_empid
        FOREIGN KEY(mgrid)
        REFERENCES Employees(empid)
    )
    CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
    SET NOCOUNT ON
    INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
    INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
    INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
    INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 
    INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
    INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
    INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00)
    INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00)
    INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00)
    INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00)
    INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
    INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
    INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
    INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)

    -----------------------------
    The first request is probably the most common one:
     returning an employee (for example, Robert whose empid=7) 
    and his/her subordinates in all levels. 
    The following CTE provides a solution to this request:

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS


      -- Anchor Member (AM)
      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 7
      UNION ALL
      
      -- Recursive Member (RM)
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE
    --------------------------------------
    Using this level counter you can limit the number of iterations
     in the recursion. For example, the following CTE is used to return 
    all employees who are two levels below Janet:

    WITH EmpCTEJanet(empid, empname, mgrid, lvl)
    AS

      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 3
      UNION ALL
      
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTEJanet as M
          ON E.mgrid = M.empid
      WHERE lvl < 2
    )
    SELECT empid, empname
    FROM EmpCTEJanet
    WHERE lvl = 2

    ------------------------------------------
    As mentioned earlier, CTEs can refer to
     local variables that are defined within the same batch.
     For example, to make the query more generic, you can use 
    variables instead of constants for employee ID and level:

    DECLARE @empid AS INT, @lvl AS INT
    SET @empid = 3 -- Janet
    SET @lvl   = 2 -- two levels
    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS

      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = @empid
      UNION ALL
      
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTE as M
          ON E.mgrid = M.empid
      WHERE lvl < @lvl
    )
    SELECT empid, empname
    FROM EmpCTE
    WHERE lvl = @lvl
    -----------------------------------------------
    Results generated thus far might be returned (but are not guaranteed to be), 
    and error 530 is generated. You might think of using the MAXRECURSION option 
    to implement the request to return employees who are two levels below 
    Janet using the MAXRECURSION hint instead of the filter in the recursive member

    WITH EmpCTE(empid, empname, mgrid, lvl)
    AS

      SELECT empid, empname, mgrid, 0
      FROM Employees
      WHERE empid = 1
      UNION ALL

      SELECT E.empid, E.empname, E.mgrid, M.lvl+1
      FROM Employees as E
        JOIN EmpCTE as M
          ON E.mgrid = M.empid
    )
    SELECT * FROM EmpCTE
    OPTION (MAXRECURSION 2)
    ------------------------------------------------

    WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
    AS

      SELECT empid, empname, mgrid, 0,
        CAST(empid AS VARBINARY(900))
      FROM Employees
      WHERE empid = 1
      UNION ALL
      SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
        CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
      FROM Employees AS E
        JOIN EmpCTE AS M
          ON E.mgrid = M.empid
    )
    SELECT
      REPLICATE(' | ', lvl)
        + '(' + (CAST(empid AS VARCHAR(10))) + ') '
        + empname AS empname
    FROM EmpCTE
    ORDER BY sortcol
    (1) Nancy
     | (2) Andrew
     |  | (5) Steven
     |  | (6) Michael
     | (3) Janet
     |  | (7) Robert
     |  |  | (11) David
     |  |  |  | (14) James
     |  |  | (12) Ron
     |  |  | (13) Dan
     |  | (8) Laura
     |  | (9) Ann

     | (4) Margaret
     |  | (10) Ina



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, June 19, 2015 4:52 AM
  • Thanks for your detailed response.

    So here we only have one Hierarchy where the root value is "Nancy"....

    What if I need to store multiple hierarchies with more than one root value ?? Meaning "Nancy" is one root, "Mark" is another root etc??? 

    Does this make sense ?

    Friday, June 19, 2015 11:23 AM
  • Do you think, using "HierarchyId" would be the easiest solution ?

    I hope it would work for multiple root nodes.

    Friday, June 19, 2015 12:03 PM
  • Multiple root nodes will work with both the adjacency list model or Hierarchyid.  eg from BOL

    CREATE TABLE SimpleDemo
    (Level hierarchyid NOT NULL,
    Location nvarchar(30) NOT NULL,
    LocationType nvarchar(9) NULL);
    
    INSERT SimpleDemo
    VALUES 
    ('/1/', 'Europe', 'Continent'),
    ('/2/', 'South America', 'Continent'),
    ('/1/1/', 'France', 'Country'),
    ('/1/1/1/', 'Paris', 'City'),
    ('/1/2/1/', 'Madrid', 'City'),
    ('/1/2/', 'Spain', 'Country'),
    ('/3/', 'Antarctica', 'Continent'),
    ('/2/1/', 'Brazil', 'Country'),
    ('/2/1/1/', 'Brasilia', 'City'),
    ('/2/1/2/', 'Bahia', 'State'),
    ('/2/1/2/1/', 'Salvador', 'City'),
    ('/3/1/', 'McMurdo Station', 'City');

    Hierarchical Data (SQL Server)

    The continents are root nodes.

    In the adjacency list model sample from above here's how to have multiple root nodes.

    CREATE TABLE Employees
     (
       empid   int         NOT NULL,
       mgrid   int         NULL,
       empname varchar(25) NOT NULL,
       salary  money       NOT NULL,
       CONSTRAINT PK_Employees PRIMARY KEY(empid),
       CONSTRAINT FK_Employees_mgrid_empid
         FOREIGN KEY(mgrid)
         REFERENCES Employees(empid)
     )
     CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
     SET NOCOUNT ON
     INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00)
     INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00)
     INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00)
     INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00) 
     INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00)
     INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00)
     INSERT INTO Employees VALUES(7 , NULL   , 'Robert'  , $2500.00)
     INSERT INTO Employees VALUES(8 , 7   , 'Laura'   , $2500.00)
     INSERT INTO Employees VALUES(9 , 7   , 'Ann'     , $2500.00)
     INSERT INTO Employees VALUES(10, 9   , 'Ina'     , $2500.00)
     INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00)
     INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00)
     INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00)
     INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00)
    Robert and Nancy are both root nodes in the hierarchy.

    In both models it's not possible to have multiple parents, but that's by definition of a hierarchy.

    David



    David http://blogs.msdn.com/b/dbrowne/

    Friday, June 19, 2015 1:38 PM
  • "Nested Set" method by Joe Celko seems like a very simple solution (with each row having a left and right value)

    Only challenge could be managing Nodes (Ie deleting , updating, inserting new nodes etc.)

    • Marked as answer by SQLDev555 Monday, June 22, 2015 6:21 AM
    Monday, June 22, 2015 6:21 AM