Use of hierarchyid in SQL Server

Posted on Posted in Database, Development, Performance, Script, SQL 2012, SQL 2014, SQL 2016, T-SQL

Russian-Matroshka_no_bg

I attended a TDWI conference in May 2016 in Chicago. Here I got a hint about the datatype hierarchyid in SQL Server which could optimize and eliminate the good old parent/child hierarchy.

Until then I (and several other in the class) hadn’t heard about the hierarchyid datatype in SQL Server.

So here’s an article covering some of the aspects of the datatype hierarchyid – including:

  • Introduction
  • How to use it
  • How to optimize data in the table
  • How to work with data in the hierarchy-structure
  • Goodies

Introduction

The datatype hierarchyid was introduced in SQL Server 2008. It is a variable length system datatype. The datatype can be used to represent a given element’s position in a hierarchy – e.g. an employee’s position within an organization.

The datatype is extremely compact. The storage is dependent in the average fanout (fanout = the number of children in all nodes). For smaller fanouts (0-7) the typical storage is about 6 x Log A * n bits. Where A is the average fanout and n in the total number of nodes in the tree. Given above formula an organization with 100,000 employees and a fanout of 6 levels will take around 38 bits – rounded to 5 bytes of total storage for the hierarchy structure.

Read the complete blogpost here:

Use of hierarchyid in SQL Server

Gem

Leave a Reply

Your email address will not be published. Required fields are marked *