SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Server # Storing Hierarchical Data – Parent Child n’th level # TSQL


Today, I would like to explain one way in which we can store the HIERARCHICAL data in SQL tables. A general table structure which people come up to store this kind of data is -


Where, EmployeeID id the UniqueID alloted to every new employee record inserted into the table and ManagerID is the EmployeeID of the immediate manager of the employee. Keeping in mind that Manager is also an employee.

Problem Statement

This table structure very well serves the purpose as long as we have 1-Level hierarchy. However, if the hierarchy is of n'th level, the SELECT statement to fetch the records becomes more complex with this kind of table structure. Suppose, we want to fetch the complete TREE of a particular employee, i.e. list of all the employees who are directly or indirectly managed by a particular employee. How to do it……..?

Thanks to CTE’s for making the life a bit easier – as using them in a recursive manner, we can get the work done. Please follow this msdn link to see an implementation using recursive CTE.

Suggested Table Structure


Here, I have just included a new column [PATH]. It is of VARCHAR(MAX) type. I have taken it as VARCHAR(MAX) just to make sure the field is long enough to store the complete path. But one can assign appropriate size as per their system’s requirement.

The basic idea of the [path] column is to store the complete hierarchical path of any employee separated by a delimiter as under -


Calculating the new path is very simple. It’s just, {New Path} = {Parent Path} + {Self ID} + {Delimiter}

Now, suppose if I want to fetch all the employees who are directly or indirectly working under EmployeeID = 2, I can use the below tsql -

SELECT 1 EmployeeID,NULL ManagerID, '\1\' [Path]
SELECT 2 EmployeeID,1 ManagerID, '\1\2\' [Path]
SELECT 3 EmployeeID,1 ManagerID, '\1\3\' [Path]
SELECT 4 EmployeeID,2 ManagerID, '\1\2\4\' [Path]
SELECT 5 EmployeeID,4 ManagerID, '\1\2\4\5\' [Path]
  [Path] LIKE '%\2\%'

We can use a simple logic to even find out the level of the Employee -

  (LEN([Path]) - LEN(REPLACE([Path],'\',''))) - 2 [Level]
  [Path] LIKE '%\2\%'


2 is subtracted from the formula as the length of delimiter for Level-0 is 2.


Hope, this simple trick could save a lot of time for the ones who find themselves lost playing with the hierarchical data.

SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP

Hemantgiri S. Goswami is working as a Consultant at Surat, INDIA. He has been a Microsoft SQL Server MVP three years running; he also moderates multiple SQL Server community forums including http://www.sql-server-performance.com and http://www.sqlserver-qa.net. He actively participates and speaks at local user group events organized under the aegis of www.surat-user-group.org, DotNetChaps and CSI Surat chapters, of which he is a founding and active member. He regularly publishes article on his blog space http://www.sql-server-citation.com. He has recently taken up a new initiative - blogging about SQL in his native language Gujarat through his blog http://sqlservercitation-gujarati.blogspot.com. He is an Author of the book SQL Server 2008 High Availability.


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...