data heirarchy

Posted on 2004-10-22
Last Modified: 2008-02-01
anybody has simple example of acheiving 'powerful' heirarchy table?

i tried with success a method, where in a column stores the whole heirarchy in every record

id    userid     parent id    level   heirarchy    
1     nick        kelvin          5       .

the problem is, if the heirarchy is VERY Large, it goes beyond the heirarchy columns limit of 8000 characters, and i have to change it to datatype of text, in which i cannot use triggers anymore, to update the column

would greatly appreciate if anyone can recommend an easier/smarter way to implement this

easy in sense of inserting. updating/deletion not neccessary
Question by:dynamicrevolutions
    LVL 12

    Assisted Solution

    imo, not on ms sql 2000.  add more columns hierarchy_part1, hierarchy_2,...
    personally, i cannot imagine a reason for so huge hierarchy.
    LVL 9

    Assisted Solution

    The usual way is to have a separate table to store the hierarchy, one row per entry. For example

    id    hierarchy
    1        1
    1        5
    1        9

    and so on.

    Have a primary key/foreign key relationship on id.

    What do you intend to do with the hierarchy?
    LVL 10

    Assisted Solution


    Extracted from BOL

    Databases often store hierarchical information. For example, consider the following table, which contains data that hierarchically represents the regions of the world.

    CREATE TABLE Hierarchy
       (Parent    VARCHAR(20) NOT NULL,
        Child VARCHAR(20),
       CONSTRAINT UIX_ParentChild
       UNIQUE NONCLUSTERED (Parent,Child))

     ON Hierarchy(Parent)

    INSERT Hierarchy VALUES('World','Europe')
    INSERT Hierarchy VALUES('World','North America')
    INSERT Hierarchy VALUES('Europe','France')
    INSERT Hierarchy VALUES('France','Paris')
    INSERT Hierarchy VALUES('North America','United States')
    INSERT Hierarchy VALUES('North America','Canada')      
    INSERT Hierarchy VALUES('United States','New York')
    INSERT Hierarchy VALUES('United States','Washington')
    INSERT Hierarchy VALUES('New York','New York City')
    INSERT Hierarchy VALUES('Washington','Redmond')

    This representation does not show clearly the structure implied by the data.

    Parent                             Child                            
    ---------------------------------- ----------------------------------
    World                              Europe                            
    World                              North America                    
    Europe                             France                            
    France                             Paris                            
    North America                      United States                    
    North America                      Canada                            
    United States                      New York                          
    United States                      Washington                        
    New York                           New York City                    
    Washington                         Redmond                          

    This example is easier to interpret:

       North America
          United States
             New York
                New York City

    The following Transact-SQL procedure expands an encoded hierarchy to any arbitrary depth. Although Transact-SQL supports recursion, it is more efficient to use a temporary table as a stack to keep track of all of the items for which processing has begun but is not complete. When processing is complete for a particular item, it is removed from the stack. New items are added to the stack as they are identified.

    CREATE PROCEDURE expand (@current char(20)) AS
       DECLARE @lvl int, @line char(20)
       CREATE TABLE #stack (item char(20), lvl int)
       INSERT INTO #stack VALUES (@current, 1)
       SELECT @lvl = 1
       WHILE @lvl > 0
             IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
                   SELECT @current = item
                   FROM #stack
                   WHERE lvl = @lvl
                   SELECT @line = space(@lvl - 1) + @current
                   PRINT @line
                   DELETE FROM #stack
                   WHERE lvl = @lvl
                      AND item = @current
                   INSERT #stack
                      SELECT Child, @lvl + 1
                      FROM Hierarchy
                      WHERE Parent = @current
                   IF @@ROWCOUNT > 0
                      SELECT @lvl = @lvl + 1
                SELECT @lvl = @lvl - 1
       END -- WHILE

    The input parameter (@current) indicates the place in the hierarchy to start. It also keeps track of the current item in the main loop.

    The local variables used are @lvl, which keeps track of the current level in the hierarchy, and @line, which is a work area used to construct the indented line.

    The SET NOCOUNT ON statement avoids cluttering the output with ROWCOUNT messages from each SELECT.

    The temporary table, #stack, is created and primed with the item identifier of the starting point in the hierarchy, and @lvl is set to match. The lvl column in #stack allows the same item to appear at multiple levels in the database. Although this situation does not apply to the geographic data in the example, it can apply in other examples.

    In this example, when @lvl is greater than 0, the procedure follows these steps:

    If there are any items in the stack at the current level (@lvl), the procedure chooses one and calls it @current.

    Indents the item @lvl spaces, and then prints the item.

    Deletes the item from the stack so it will not be processed again, and then adds all its child items to the stack at the next level (@lvl + 1). This is the only place where the hierarchy table (#stack) is used.
    With a conventional programming language, you would have to find each child item and add it to the stack individually. With Transact-SQL, you can find all child items and add them with a single statement, avoiding another nested loop.

    If there are child items (IF @@ROWCOUNT > 0), descends one level to process them (@lvl = @lvl + 1); otherwise, continues processing at the current level.

    If there are no items on the stack awaiting processing at the current level, goes back one level to see if there are any awaiting processing at the previous level (@lvl = @lvl - 1). When there is no previous level, the expansion is complete.
    Executing the procedure expand with different parameters will return result sets illustrating the level in the hierarchy in which the specified parameter belongs.

    EXEC expand 'World'

    --This is the result set.
       North America
          United States
             New York
                New York City

    EXEC expand 'United States'

    --This is the result set.
    United States
       New York
          New York City
    LVL 6

    Accepted Solution

    Buy this book by Joe Celko:

    It's full of ideas on how to handle hierachies

    Do a Google lookup on Tropashko, he has some excellent ideas on how to handle materialized path hierarchy model

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now