Selecting from hierarchies like Managers and Employees

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    Comments posted to this topic are about the item Selecting from hierarchies like Managers and Employees

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • miika.langille

    SSC Rookie

    Points: 26

    Isn't this same function much easier to realise with an rCTE? Does an rCTE have some hidden overhead which makes manual looping better?

    For downstream selection, you could have something along the lines of:

    WITH employmentTree

    (EmpId, EmpName, SupId, [Level])

    AS (

    -- Anchor

    SELECT EmpId, EmpName, SupId, 0

    FROM Employees

    WHERE EmpId = @targetEmpId

    -- Recursive part

    UNION ALL

    SELECT emp.EmpId, emp.EmpName, emp.SupId, eTree.[Level] + 1

    FROM Employees emp INNER JOIN employmentTree eTree

    ON emp.SupId = eTree.EmpId)

    SELECT EmpId, EmpName, SupId, [Level]

    FROM employmentTree

  • imarran

    SSC-Addicted

    Points: 489

    yes, I have done this often with rCTE's which is much faster 🙂

  • novak 32871

    Valued Member

    Points: 66

    Why not simply use hierarchyid if it's T-SQL ?

  • Dave Brooking

    Ten Centuries

    Points: 1373

    The HIERARCHYID datatype was only introduced to T-SQL in 2008. If you have to use an earlier version of SQL Server (2005 or earlier), hierarchyid is not supported.

    Dave

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    Isn't this same function much easier to realise with an rCTE?

    Thank you Miika.

    I think the choice of approach must depend upon the environment. For a shop whose TSQL writers are at the expert level I like your solution better than mine. In a shop that has few expert level writers the rCTE would probably be difficult for many of them to read, making the code actually less maintainable.

    I should admit that I had not thought about rCTE.

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • novak 32871

    Valued Member

    Points: 66

    I know but there is already SQL 2012 out. It is good exercise but 2000/2003 has it's age and most of clients already moved. But I understand your point.

  • imarran

    SSC-Addicted

    Points: 489

    I also have a solution in the form of a view that you can join to that will display upline and down line structure depending on how it is joined to

    Its quite technical but if you guys are keen I will submit it through an article

  • Dave Brooking

    Ten Centuries

    Points: 1373

    Unfortunately, I have to work with a 3rd party application that has only recently been certified by the supplier to work with SQL Server 2008. The base application probably contains none of the datatypes that have been introduced to TSQL since SQL Server 2000. Some of the companies using the application are still running it on SQL Server 2000. We are running SQL Server 2005, so we can at least use CTEs for reporting.

    If we want to take advantage of any of the newer datatypes we would have to introduce our own tables, the links with each table could possibly break with each new release of the application. I don't think introducing tables containing HIERARCHYID columns worth it at the moment as there are other ways to report on the data (we are currently using recursive CTEs). However I am considering adding tables containing spatial datatypes as I think the benefit could outweigh the effort and also the risk of a new release breaking the table linkage.

    I'd be interested in knowing which version(s) of SQL Server Chuck is using/supporting.

    Dave

  • Jonathan AC Roberts

    SSCoach

    Points: 16992

    If you are using a recursive CTE then you will need a to have a UNION ALL.

    DECLARE @EmpId int

    SET @EmpId = 5

    ;WITH CTE AS

    (

    SELECT *

    FROM employee e

    WHERE e.EmpId = @EmpId

    UNION ALL

    SELECT e.*

    FROM employee e

    INNER JOIN CTE

    ON CTE.SupId = e.EmpId

    )

    SELECT *

    FROM CTE

    OPTION (MAXRECURSION 0)

    You can also add MAXRECURSION 0 if you are confident that there is no infinite loop in the expression or data, if there is it could lead to an infinite loop, e.g. if you had an insert for Baker like this:

    INSERT INTO Employee (EmpID, EmpName, SupID)

    VALUES (3, 'Baker', 5)

    So maybe best to stick to the default MAXRECURSION of 100 as I doubt there is any organisation with more than 100 levels.

  • ssandler

    SSC Enthusiast

    Points: 101

    I use recursive CTEs for this as well and it's quite fast. Haven't tried the hierarchyid type though.

  • Chuck Hoffman-291200

    SSC Veteran

    Points: 234

    Dave Brooking (5/3/2012)


    I'd be interested in knowing which version(s) of SQL Server Chuck is using/supporting.

    2000, 2005, and 2008.

    Chuck Hoffman
    ------------
    I'm not sure why we're here, but I am sure that while
    we're here we're supposed to help each other
    ------------

  • hayden-508426

    SSC Rookie

    Points: 34

    I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on my perf tests due to the majority of its functions being CLR.

  • Jeff Moden

    SSC Guru

    Points: 995109

    imarran (5/3/2012)


    yes, I have done this often with rCTE's which is much faster 🙂

    Proof!!! Let's see the proof for your claim!!!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995109

    hayden-508426 (5/3/2012)


    I use rCTE for account control and access on our websites. Best way to go, haven't found one that's faster, including hierarchyid. I'm thinking hierarchyid loses on my perf tests due to the majority of its functions being CLR.

    Your performance tests? Care to share those please? 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 50 total)

You must be logged in to reply to this topic. Login to reply