{"id":78417,"date":"2018-04-23T20:36:08","date_gmt":"2018-04-23T20:36:08","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=78417"},"modified":"2026-03-12T13:32:23","modified_gmt":"2026-03-12T13:32:23","slug":"sql-server-graph-databases-part-4-working-hierarchical-data-graph-database","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-graph-databases-part-4-working-hierarchical-data-graph-database\/","title":{"rendered":"Graph Databases for Hierarchical Data in SQL Server (Part 4)"},"content":{"rendered":"

The series so far:<\/h4>\n
    \n
  1. SQL Server Graph Databases - Part 1: Introduction<\/a><\/li>\n
  2. SQL Server Graph Databases - Part 2: Querying Data in a Graph Database<\/a><\/li>\n
  3. SQL Server Graph Databases - Part 3: Modifying Data in a Graph Database<\/a><\/li>\n
  4. SQL Server Graph Databases - Part 4: Working with hierarchical data in a graph database<\/a><\/li>\n
  5. SQL Server Graph Databases - Part 5:\u00a0Importing Relational Data into a Graph Database<\/a><\/li>\n<\/ol>\n\n

    SQL Server graph databases can model hierarchical data (org charts, category trees, parts lists) using node tables for entities and edge tables for parent-child relationships. The MATCH clause navigates these hierarchies without recursive CTEs or self-joins. However, SQL Server\u2019s graph implementation has limitations for multi-hop traversals – MATCH patterns must be explicitly defined for each depth level.<\/p>\n

    For hierarchies of unknown depth, a closure table workaround (storing all ancestor-descendant paths in a separate table) provides the flexibility that MATCH alone cannot. This article compares graph approaches to HierarchyID and closure tables for hierarchy modeling in SQL Server.<\/p>\n

    Introduction<\/h2>\n

    This first three articles in this series focused on using SQL Server graph databases to work with data sets that contained relationships not easily handled in a typical relational structure, the types of relationships you might find in a social network site or product recommendation engine. One of the topics that the articles touched upon was how to work with hierarchical data and their relationships, as they applied to the FishGraph<\/strong> database used in the examples.<\/p>\n

    This article digs deeper into the topic of hierarchies, particularly those that contain more complex relationships than what you saw in the examples. The article first provides an overview of how to create and populate the tables necessary to support a hierarchy and then focuses on how to query hierarchical data that contains multiple types of relationships.<\/p>\n

    Limitations of the HierarchyID Data Type<\/h2>\n

    Working with hierarchical data is certainly not new in SQL Server. Since the product\u2019s early days, you could represent a hierarchical structure by including a foreign key in a table that pointed to the primary key in the same table. In this way, you were able to represent the parent-child relationships that existed within the data set.<\/p>\n

    Since the release of SQL Server 2008, you\u2019ve been able to use the hierarchyid<\/strong> data type to represent a record\u2019s hierarchical position by defining values that reflect the relationships between a table\u2019s rows. For example, the data type makes it possible to represent how employees are organized within a company\u2019s hierarchical structure, such as the one shown in the following figure.<\/p>\n

    <\/p>\n

    Although this is a very simple example, it demonstrates the types of relationships that typically make up a basic hierarchy:<\/p>\n