{"id":511,"date":"2019-03-01T11:20:19","date_gmt":"2019-03-01T11:20:19","guid":{"rendered":"https:\/\/int64software.com\/blog\/?p=511"},"modified":"2019-03-01T11:20:21","modified_gmt":"2019-03-01T11:20:21","slug":"handling-hierarchical-data-structures-in-sql-server","status":"publish","type":"post","link":"https:\/\/int64software.com\/blog\/2019\/03\/01\/handling-hierarchical-data-structures-in-sql-server\/","title":{"rendered":"Handling Hierarchical Data Structures in SQL Server"},"content":{"rendered":"\n

Databases provide a great way to store and query large amounts of data quickly and efficiently. But what if you want to store something like a file system, where hierarchical links between the data are key?<\/p>\n\n\n\n

In this article I’ll be looking at how to store and query data in SQL Server in such a way that using it hierarchically is simple.<\/p>\n\n\n\n

This is specifically written for SQL Server, but the core functionality (CTEs) should work in many other SQL flavours including MySQL 8 and SQLite<\/p>\n\n\n\n

\n\n\n\n

Data Structure<\/h2>\n\n\n\n

For this example, I’ve constructed a very simple table “folder” as shown below.<\/p>\n\n\n\n

\"Example
Example Table “folder”<\/figcaption><\/figure><\/div>\n\n\n\n
CREATE TABLE folder (\n\tfolderid INT IDENTITY(1,1) PRIMARY KEY,\n\tparentid INT NULL,\n\tname VARCHAR(200) NOT NULL,\n\tcreated DATETIME NOT NULL DEFAULT(GETDATE()),\n\tlastupdate DATETIME NOT NULL DEFAULT(GETDATE()),\n\tFOREIGN KEY (parentid) REFERENCES folder(folderid)\n\t);<\/pre>\n\n\n\n
The properties of this table are:<\/p>\n\n\n\n
  1. The table has a integer primary key “folderid” which, in this case, is auto-incrementing. <\/li>
  2. It also has a foreign key “parentid” which may be NULL, indicating it has no parent and is thus a “root” folder, or an integer value referencing another row in the folder table by its primary key.<\/li>
  3. Each folder has a name to more easily identify it, and created and last update datetime values for flavour.<\/li><\/ol>\n\n\n\n
    \n\n\n\n
    Example Data<\/h3>\n\n\n\n
    For this example, I’ve added a series of rows to the table to show it working.<\/p>\n\n\n\n
    \"Table
    Table Example Data<\/figcaption><\/figure><\/div>\n\n\n\n
    I’ve tried to name the child folders in such a way that it is easy to see its intended path quickly and easily. “Child 1-2<\/em>” for example is the second child of “Child 1<\/em>“, which in turn is the first child of the root folder “Root Folder 1<\/em>“.<\/p>\n\n\n\n
    \n\n\n\n
    Querying the Data<\/h2>\n\n\n\n
    The Problem<\/h3>\n\n\n\n
    The data as it’s shown in the above Table Example Data screenshot is the result of a straight SELECT statement. It shows all of the data correctly, and would be fine if you are looking to get ALL of the data simply.<\/p>\n\n\n\n
    However, consider the scenario where you only want the folder “Child 1” and its descendents. I can get “Child 1” itself easy enough with a basic SELECT:<\/p>\n\n\n\n
    <\/p>\n\n\n\n
    SELECT folderid, parentid, name \nFROM folder \nWHERE folderid = 2;<\/pre>\n\n\n\n
    I can also get its children with a similarly simple query. But what about its children’s children, or its children’s children’s children? And I want it all in one neat query with the results properly sorted. Time to introduce the Common Table Expression.<\/p>\n\n\n\n
    \n\n\n\n
    Common Table Expressions (CTE)<\/h3>\n\n\n\n
    A CTE is used to create a temporary results set for the lifetime of one SELECT (or INSERT, UPDATE, DELETE or MERGE) statement, so like a temporary table. Where CTEs get their real power though is that they can be self-referential and recursive.<\/p>\n\n\n\n
    What that means in our scenario is that we can use a CTE to build a result set based on a recursive query, or automatically drill down into a hierarchical structure parent to child in a single query.<\/p>\n\n\n\n
    CTEs are defined with the “WITH” command, followed by a single SELECT (in this example) statement, such as:<\/p>\n\n\n\n
    <\/p>\n\n\n\n
    WITH cte_name AS ( SELECT folderid, parentid FROM folder )\nSELECT *\nFROM cte_name;<\/pre>\n\n\n\n
    This simple example creates a CTE named “cte_name<\/em>” and fills it with folderid <\/em>and parentid <\/em>from the folder <\/em>table, and then selects the results of that. A fairly pointless example, but it shows the syntax so we can move on to something more complicated.<\/p>\n\n\n\n
    \n\n\n\n
    Get Folder Structure<\/h3>\n\n\n\n
    Let’s look at our scenario again.<\/p>\n\n\n\n
    We want to be able to query the “folder” table to find one particular folder row and all of its descendents.<\/p>\n\n\n\n
    <\/p>\n\n\n\n
    \/*\nUsing a Recursive Common Table Expression (CTE) to retrieve\na hierarchical list of child folders starting from a source \ncontainer given by @sourceid.\n*\/\nDECLARE @sourceid INT = 2;\n\nWITH cte (folderid, parentid, name, created, lastupdate, level, path) AS (\n    SELECT folderid, parentid, name, created, lastupdate, \n           0 AS level, CAST(name AS VARCHAR(1000)) AS path\n    FROM folder \n    WHERE folderid = @sourceid \n    UNION ALL \n    SELECT c.folderid, c.parentid, c.name, c.created, c.lastupdate, \n           cte.level + 1 AS level, \n           CAST((cte.path + '\/' + c.name) AS VARCHAR(1000)) AS path\n    FROM folder c \n    INNER JOIN cte ON cte.folderid = c.parentid \n) \nSELECT folderid, parentid, name, created, lastupdate, level, path\nFROM cte\nORDER BY path ASC<\/pre>\n\n\n\n
    It may seem daunting at first, but this is really very simple. It will first retrieve the row with folderid matching the “@sourceid” variable.<\/p>\n\n\n\n
    It then performs a UNION to also select the children of the data in the CTE recursively, building the hierarchical tree of data. Note that the second SELECT statement performs an INNER JOIN on the CTE itself, linking the current result’s “parentid<\/em>” field to the CTE result’s “folderid<\/em>“.<\/p>\n\n\n\n
    <\/p>\n\n\n\n
    \"Get
    Get Folder Structure Results<\/figcaption><\/figure>\n\n\n\n
    Here you can see we also used the CTE’s SELECT statements to inject two additional useful fields into the data: “level” and “path”.<\/p>\n\n\n\n