Working with Hierarchical Data

Retrieve Hierarchical Data from Database

Hierarchical Data Model is proven modeling technique to implement hierarchical business entities like (GL Hierarchy, Organization Hierarchy, Menu Hierarchy etc…). We can also use it to retrieve role based menu from database. Ideally hierarchical data is representing by level (depth) and consider it should use Tree Data Structure Algorithm. Now it is important to design such table using Hierarchical Data Modeling and creating queries which will return hierarchical data in optimized manner.

Create a hierarchical table like below (Geographic Location Hierarchy).

Insert some records in this table. (Please find insert script in attached SQL file)

Now it is important to retrieve data of this table in hierarchical format. That means if we are requesting for particular Geographic Location (say ‘India’) then query should return all child-grand child records for India. To achieve this we will use CTE (Common Table Expression). Please refer http://msdn.microsoft.com/en-us/library/ms190766.aspx for other usage of CTE.

Consider that you have requirement where you want return Geographic Location tree by passing GLCd. To achieve this you need to write CTE query. CTE query are bit complex to understand so we will create some functions to simplify this query.

We will create GLSubordinates function, which accept GLCd (GL Code). Please find create script for this function in attached SQL file.

SELECT * FROM GLSubordinates('IND')

Above example, returns all the child-grandchild Geographic Location for GLCD = ‘IND’.

NodeSequence is optional field, it is just showing depth of tree you can see 2, 2.5, 2.5.9, 2.5.10 etc…

CTE Queries performs quite efficiently. With index on GLID and GLCD with data up to 12 hierarchical levels for 24000 records this query is returning data less than a second.

By default recursion limit for CTE queries is 100 levels. And I guess it is enough level for normal hierarchical data, but you can also change it. Please refer http://msdn2.microsoft.com/en-us/library/ms181714.aspx.

Usage

You can use this function directly or in your queries. By adding Level check, you can customize this function to return tree up to specified level.

SQL Script

CREATE TABLE [GLHierarchy](

[GUID] [int] IDENTITY(1,1) NOT NULL,

[GUCD] [varchar](10) NOT NULL,

[GUDesc] [varchar](50) NOT NULL,

[ParentGUCD] [varchar](10) NULL

) ON [PRIMARY]

/* Insert queries */

INSERT INTO [GLHierarchy] Values ('AP0','Asia-Pacific',null)

-- Level 2 Records -- 

INSERT INTO [GLHierarchy] Values ('IND','India','AP0')

INSERT INTO [GLHierarchy] Values ('PHL','Phillipines','AP0')

INSERT INTO [GLHierarchy] Values ('CHN','China','AP0')

-- Level 3 Records -- 

INSERT INTO [GLHierarchy] Values ('MAH','Maharashtra','IND')

INSERT INTO [GLHierarchy] Values ('KAR','Karnataka','IND')

INSERT INTO [GLHierarchy] Values ('GUJ','Gujarat','IND')

INSERT INTO [GLHierarchy] Values ('MNL','Manila','PHL')

-- Level 4 Records --

INSERT INTO [GLHierarchy] Values ('BOM','Mumbai','MAH')

INSERT INTO [GLHierarchy] Values ('PUN','Pune','MAH')

INSERT INTO [GLHierarchy] Values ('BAN','Bangalore','KAR')

----- create GLSubordinates function using CTE

if object_id( 'GLSubordinates', 'IF' ) is not null drop function GLSubordinates

GO

create function GLSubordinates( @GLCD varchar(10) ) 

returns table as return with GLSubnodes( distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence )

AS ( select 0, h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(80), ltrim(str(h.GLID))) as NodeSequence 

from [GLHierarchy] h 

where h.GLCD = @GLCD 

union all 

select distance+1,  h.GLID, h.GLCD, h.GLDesc, h.ParentGLCD, convert( varchar(80), sn.NodeSequence+'.'+ltrim(str(h.GLID))) 

from [GLHierarchy] h inner join GLSubnodes sn on h.ParentGLCD = sn.GLCD)

select distance, GLID, GLCD, GLDesc, ParentGLCD, NodeSequence from GLSubnodes

GO

--- Test Scripts ---

select * from GLSubordinates('IND')