Home Software Blog Wallpapers Webtools
SQL Server Hierarchical data
Tuesday 11, October 2016   |   Post link   |   Sample code  Download related material

It's not everyday I need to store hierarchical data in SQL tables but when I do, the usual self-join on a table works out fine. For example, if I'm storing file system folders in a SQL database, I'll probably define a table like this:

create table Folder
	Id int Primary Key Identity(1,1),
	Name varchar(256) Not Null,
	Path varchar(Max) Not Null,	
	ParentId Int Null	

create index IX_Folder_ParentId on Folder (ParentId)

If I'm storing my humble music collection the output might look like this:

sample output

I can now find out which folder belong to whom. For something trickier like finding the the entire hierarchy below a particular folder, I can use a CTE like this:

-- I want to list all albums of Guns n' Roses  	
declare @folderId int = 335;
with FolderHierarchy (id, name, path, parentid, level) as 
	select Id, Name, Path, ParentId, 0 as level from Folder where Id = @folderId
	union all
	select f.Id, f.Name, f.Path, f.ParentId, level + 1 from Folder f
	inner join FolderHierarchy h
	on f.ParentId = h.Id
select * from FolderHierarchy order by level

As it turns out this isn't the only way to represent hierarchical data. SQL Server since version 2008 has specific datatype for such scenarios, its the 'hierarchyid' type. If I now introduce a new column for it, the table can be created like this:

if exists(select * from sys.tables where name = 'Folder')
drop table Folder 

create table Folder
	Id int Primary Key Identity(1,1),
	Name varchar(256) Not Null,
	Path varchar(Max) Not Null,	
	ParentId Int Null, -- No longer needed but I've kept it to avoid making two tables for illustration  
	HId HierarchyId Null

create index IX_Folder_ParentId on Folder (ParentId)

create unique index UQ_Folder_HId on Folder (HId)

The HId column is supposed to store hierarchy in a particular format. The format is:
The root item should have the value /. The first child of the root can be /1/, the second child can be /2/. There can be a child inserted between 1 and 2 as '/1.5/'. If 1 has a child, it can be /1/1/ and so on. These values can computed at the application side and inserted into the table or SQL functions can help you generate these. In my case I have decided to generate these values in the sample C# application itself.

Once the hierarchy is correctly setup we can perform things like listing all direct or indirect descendant quite easily. For example, instead of using the CTE we can now use:

select *, cast(hid as varchar(max)) from Folder where HId.IsDescendantOf('/4/40/') = 1

What about doing the reverse which is listing all direct and indirect parents? The code below shows both approaches.

-- Find all ancestors using CTE
with FolderHierarchy (id, name, path, parentid) as 
	select Id, Name, Path, ParentId from Folder where ID = 335
	union all
	select f1.Id, f1.Name, f1.Path, f1.ParentId from Folder f1
	inner join FolderHierarchy f2 on f1.id = f2.ParentId
select * from FolderHierarchy

-- Find all ancestors using hierarchy
select f1.*, cast(f1.hid as varchar(max)) as Path, f1.hid.GetLevel() as Level from Folder f1 
inner join Folder f2 on f2.HId.IsDescendantOf(f1.HId) = 1 
where f2.Id = 335

So which is better? Well the hierarchyid datatype allows quite a few other operations like moving entire hierarchy under a different node but this should be easy using the traditional approach too. What about performance? Here is screenshot of the plan comparison for a small number (1000 odd) of records.

simple plan comparison

It looks like using hierarchyid results in a much simpler plan and is 2 times better. However when I tried with greater number of records the difference was not too much.

plan comparison more records

For the query where we listed direct and indirect ancestors, the difference is reverse, using hierarchyid makes the performance worse.

ancestor plan comparison

The sample application which reads a file system folder and populates the Folder table is present in the ZIP file. The ZIP file also contains the database.sql file which contains all the T-SQL code discussed here.

Sample code  Download related material


Posts By Year

2023 (5)
2022 (10)
2021 (5)
2020 (12)
2019 (6)
2018 (8)
2017 (11)
2016 (6)
2015 (17)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (4)
.NET Core (2)
AWS (5)
AWS API Gateway (1)
Android (1)
Apache Camel (1)
Architecture (1)
Audio (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (2)
CloudHSM (1)
Containers (4)
Corporate culture (1)
Database (3)
Database migration (1)
Desktop (1)
Docker (1)
DotNet (3)
DotNet Core (2)
ElasticSearch (1)
Entity Framework (3)
Git (3)
IIS (1)
JDBC (1)
Java (9)
Kibana (1)
Kubernetes (1)
Lambda (1)
Learning (1)
Life (7)
Linux (1)
Lucene (1)
Multi-threading (1)
Music (1)
OData (1)
Office (1)
PHP (1)
Photography (1)
PowerShell (2)
Programming (28)
Rants (5)
SQL (2)
SQL Server (1)
Security (2)
Software Engineering (1)
Software development (2)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Unit Tests (1)
Utilities (3)
VC++ (1)
VMWare (1)
VSCode (1)
Visual Studio (2)
Wallpapers (1)
Web API (2)
Win32 (1)
Windows (9)
XML (2)

Posts By Tags

.NET(6) API Gateway(1) ASP.NET(4) AWS(3) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Asynch(1) Authentication(2) Azure(2) Backup(1) Beliefs(1) BlockingQueue(1) Book review(2) Books(1) Busy(1) C#(4) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Camel(1) Certificates(1) Checkbox(1) CloudHSM(1) Cmdlet(1) Company culture(1) Complexity(1) Consumer(1) Consumerism(1) Containers(3) Core(2) Custom(2) DPI(1) Data-time(1) Database(4) Debugging(1) Delegates(1) Developer(2) Dockers(2) DotNetCore(3) EF 1.0(1) Earphones(1) Elastic Search(1) ElasticSearch(1) Encrypted(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JDBC(1) JSON(1) JUnit(1) JWT(1) Java(3) JavaScript(1) Kubernetes(1) Life(1) LinkedIn(1) Linux(2) Localization(1) Log4J(1) Log4J2(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mirror(1) Mobile Apps(1) Modern Life(1) Money(1) Music(1) NGINX(1) NTFS(1) NUnit(2) OData(1) OPENXML(1) Objects(1) Office(1) OpenCover(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Producer(1) Programming(2) Python(2) QAAC(1) Quality(1) REDIS(2) REST(1) Runtimes(1) S3-Select(1) SD card(1) SLF4J(1) SQL(2) SQL Code-first Migration(1) SSH(2) Sattelite assemblies(1) School(1) Secrets Manager(1) Self reliance(1) Service(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Spring(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Ubuntu(1) Url rewrite(1) VMWare(1) VSCode(1) Validation(2) VeraCode(1) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(2) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1) iTunes(1) renew(1) security(1)