Post reply

hierarchial query in sql server 2005

  • March 31, 2012 at 11:32 am

    #259340

    Hi,

    Can i get sample code for hierarcial query in sql ser 2008.My Scenario is as follows:

    I have a table with all the employees name and their manager's name.Now i need a to fetch the hierarchial data of all the employees.

    Thanks in advance

    Regards,

    Pavan K

  • March 31, 2012 at 3:00 pm

    #1467347

    Sure, if you don't mind an example with EmployeeIDs and Names. See the following link.

    http://www.sqlservercentral.com/articles/T-SQL/72503/

    I also recommend you lookup "Recursive CTEs". I'd recommend looking up the "HierarchyID" except thats only available in 2008 and above.

    Last but not least, if you're really going to get into hierarchies, I recommend you lookup "Nested Set Hierarchy" or "Nested Set Tree". A fellow by the name of Joe Celko made the technique quite popular back in '99.

    --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)

  • April 12, 2012 at 12:27 pm

    #1472865

    What is the performance with CTE/Hierarcy when it comes to more than Millions of records getting inserted into table?

  • April 12, 2012 at 5:12 pm

    #1472997

    sukruti.patel (4/12/2012)

    What is the performance with CTE/Hierarcy when it comes to more than Millions of records getting inserted into table?

    Provided that the parent/child information is precalculated and included in such Adjacency Lists, inserts take no longer than with any other table.

    Now, since you brought it up, please tell me of at least one hierarchy that you've worked with that contains "millions of records". Except for those working with succssful MLM's or email tree analysis for a large company over several years, most people never see a hierarchy more than a couple of thousand nodes. Even the BOM for a 747 isn't listed "in the millions".

    --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)

Viewing 4 posts - 1 through 4 (of 4 total)

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