Another Way to Look at Trees

  • Comments posted to this topic are about the item Another Way to Look at Trees

  • Larry - are you aware that since SQL Server 2008 R2 there is a data type HierarchyID?

    If you using anything older than that version there is a Joe Celco book 100% dedicated to the

    topic of hierarchies, trees and graphs 😉

  • Can change CTE limit with OPTION statement. Example : SELECT * FROM CTE OPTION (MAXRECURSION 123)

    If no manager / supervisor then code won't execute the loop

    Add SET @NRows = @@RowCount before WHILE ( @NRows > 0 )

    One SELECT statement less but one iteration more

    Remove SET @Nrows = statement in the loop but add SET @NRows = @@RowCount before SET @Nlvl = @Nlvl + 1

    Should score a few nano seconds. 🙂

  • Hello,

    execution of:

    select EmployeeID, LastName, FirstName, MiddleName, Department, 0 as EmployeeLevel

    into #tmptbl

    from Employees where SupervisorID = (Select EmployeeID from #tmptbl where EmployeeLevel = 1)

    gives error message:

    The SELECT INTO statement cannot have same source and destination tables.


    Regards, Klaus

  • SELECT INTO will create a new table and it exists already since you are selecting from it.

    Change the statement to something you have done

    INSERT INTO #Tmp (col1,col2)

    SELECT col1,col2 FROM #Tmp WHERE col1=1

  • Isn't this a DIY version of recursive CTE?

  • It is dyi version of a recvursive cte but avoids the rbar problem and potential exponential runtime

    Gerald Britton, Pluralsight courses

  • Sorry, but what means "DIY"?

  • klaus.kuehne (9/15/2014)

    Sorry, but what means "DIY"?

    DIY means Do It Yourself.

    HTH (Hope This Helps 🙂 )

    - webrunner

    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"

  • I'm reminded of the saying "Make things as simple as possible but not simpler". In other words, you may have assumed too simple of an answer.

    An accepted hierarchy for the animal kingdom is as follows: kingdom, phylum, class, order, family, genus, species

    There's one problem with it. Biologists have already created spreadsheets with "super class" and "sub class" columns to handle new generalizations in-between the levels of an accepted hierarchy. But, their software won't allow them to enter their new data.

    By going "backwards" in intelligence from a relative solution to an absolute solution, and backwards from complex to simple, you have gone from a general solution to a specific solution. If you must have performance, then it was a good choice but the price will be paid in maintenance.

    I don't have a problem with loopy code which generally outperforms CTEs in depths over four.

  • Larry,

    While this is useful, a much more robust and much faster version is discussed in I personally worked on a project where we had over 20 levels with hundreds of thousands of nodes, and our processing was in the 1-2 second range using the Nested Set implementation. Additionally, it provides the structure for holding together so much more important information, suppose you wanted to track employee time accumulated per team?

  • Thanks for your reply! I'd be very interested in looking at the book mentioned - as so often happens, the "tyranny of the urgent" resulted in doing some quick Internet scans, searches on, etc. Too bad, too, because our IT organization licenses the content on a service that makes available virtually every technical book published - if only we had more time to read some!



  • Thanks for your reply! The one other thing you'd have to remember is to up the MaxRecursion when someone ended up with more than 123 direct reports (:-D) Great points, though!


  • Thanks for your reply, Bill! Believe it or not, it did cross my mind, however briefly, that "modern management" (is it really new?!) styles might have some new and varied organizational structures, cross-functional teams, etc.

    Somehow, though, at the end of the day pretty much all of us have someone we directly report to and who evaluates our performance . . . with that one exception up at level 0! And even there, a board of directors, trustees, whatever, may fill that role.


  • fregatepllada (9/14/2014)

    Larry - are you aware that since SQL Server 2008 R2 there is a data type HierarchyID?

    If you using anything older than that version there is a Joe Celco book 100% dedicated to the

    topic of hierarchies, trees and graphs 😉

    I'm curious... are you using the HierarchyID datatype for anything now?

    --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.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

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

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