{"id":78129,"date":"2018-04-10T13:13:52","date_gmt":"2018-04-10T13:13:52","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=78129"},"modified":"2026-03-09T14:55:20","modified_gmt":"2026-03-09T14:55:20","slug":"sql-server-closure-tables","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sql-server-closure-tables\/","title":{"rendered":"SQL Server Closure Tables: Model Hierarchies in SQL"},"content":{"rendered":"\n

A closure table is a design pattern for representing hierarchies in SQL databases using two tables: one for entities (nodes) and one for relationships (edges). Unlike self-referencing tables (parent_id), a closure table stores every ancestor-descendant path, making it straightforward to query subtrees, find ancestors, calculate depths, and track relationships that change over time.<\/p>\n\n\n\n

This approach uses standard SQL JOINs and GROUP BY – no recursive CTEs or HIERARCHYID required. This article demonstrates converting the AdventureWorks Employee hierarchy from a self-referencing table to a closure table pattern.<\/p>\n\n\n\n

Introduction<\/h2>\n\n\n\n

You\u2019ll find self-referencing tables being used to represent hierarchies in SQL databases, and they seem like an elegant recursive way to do it: AdventureWorks2016 has three of them, including one to map the staff hierarchy. The problem is, of course, that such an approach mixes relationships and values. Before we get too conceptual about this difficulty, just imagine how you\u2019d deal with a sudden request from your AdventureWorks bosses to be able to track the corporate hierarchy over time, or to work out when, and for how long, Tim was reporting to Alice. Yes, the information isn\u2019t there, and you can\u2019t deal with it in a self-referencing table even if you had the information. Relationships can have attributes as well as entities.<\/p>\n\n\n\n

The Closure Table pattern<\/h2>\n\n\n\n

Real-life hierarchies need more than a parent-child relationship. Such hierarchies are often best modelled in SQL Server by having one table for the nodes and another for the \u2018edges\u2019, the relations between them. This ‘Closure Table’ pattern is much more suitable for real-life hierarchies that change over time or have conditions or other subtleties in the nature of the branches. You can add attributes of the relationship. In real life, for example, relationships tend have a beginning and an end, and this often needs to be recorded, so your database can tell you the state of the hierarchy at a certain point in time.<\/p>\n\n\n\n

\n

EDITOR\u2019S NOTE: SQL Server 2017 has a new feature called Graph Databases which is similar to the techniques explained here. To learn more about this feature, take a look at the series by Robert Sheldon<\/a>.<\/p>\n<\/div>\n\n\n\n

Closure tables are plain ordinary relational tables that are designed to work easily with relational operations. It is true that useful extensions are provided for SQL Server to deal with hierarchies. The HIERARCHYID<\/strong> data type and the common language runtime (CLR) SqlHierarchyId<\/strong> class are provided to support the Path Enumeration method of representing hierarchies and are intended to make tree structures represented by self-referencing tables more efficient, but they are likely to be appropriate for some but not all the practical real-life hierarchies or directories. As well as path enumerations, there are also the well-known design patterns of Nested Sets and Adjacency Lists. In this article, we\u2019ll concentrate on closure tables.<\/p>\n\n\n\n

A directed acyclic graph (DAG) is a more general version of a closure table. You can use a closure table for a tree structure where there is only one trunk, because a branch or leaf can only have one trunk. We just have a table that has the nodes (e.g. staff member or directory \u2018folder\u2019) and edges (the relationships). We are representing an acyclic (no loops allowed) connected graph where the edges must all be unique, and where there is reflexive closure. (each node has an edge pointing to itself).<\/p>\n\n\n\n

Read also:<\/strong>
Subqueries for hierarchy traversal<\/a>
Temporal tables for tracking hierarchy changes<\/a><\/p>\n\n\n\n

An Example of a Closure Table<\/h2>\n\n\n\n

Vadim Tropashko, in his book SQL Design Patterns<\/a> (Rampant Techpress, Kittrell, NC, USA, 2006.) gave a good explanation of the Closure pattern, and it has since been described in more detail in Bill Karwin\u2019s SQL Antipatterns<\/a> (The Pragmatic Bookshelf Dallas Texas).<\/p>\n\n\n\n

We won\u2019t go into the theory. We\u2019ll just get stuck into demonstrating it with data from AdventureWorks2016 and so you can try things out.<\/p>\n\n\n\n

Preparation<\/h3>\n\n\n\n

We are going to convert the AdventureWorks Employee table from a hierarchy path to a closure table. Then we will see if we can insert a department, delete a manager, insert someone and so on, just to see how hard it is.<\/p>\n\n\n\n

  --create our temporary working staff hierarchy table\n  DROP TABLE IF EXISTS #staff\n  CREATE TABLE #Staff\n    (\n    Employee_ID INT IDENTITY PRIMARY KEY,--we will use this as the key\n    OrganizationNode HIERARCHYID,\n    Ancestors AS (Coalesce(OrganizationNode.GetLevel(),0)) PERSISTED,\n  --we will use this to get the number of elements in the \n    employee NVARCHAR(80),\n    FirstName NVARCHAR(50),\n    LastName NVARCHAR(50)\n    );<\/pre>\n\n\n\n
We fill this with the data from AdventureWorks<\/p>\n\n\n\n
  --steal the data from Adventureworks\n  INSERT INTO #Staff\n    (OrganizationNode, employee, FirstName, Lastname)\n  SELECT \n      Coalesce(Employee.OrganizationNode, Convert(HIERARCHYID,'\/')), \n  \t  --we convert the null into a valid hierarchy root\n      Coalesce(Person.Title+' ','')+ Person.FirstName+' ' +Coalesce(Person.MiddleName+' ','')\n        + Person.LastName+Coalesce(' '+Person.Suffix,'')+ '. '+ Employee.JobTitle,\n  \t  --we have the full name\n  \t  Person.FirstName,\n  \t  Person.LastName\n    FROM AdventureWorks2016.HumanResources.Employee Employee\n      INNER JOIN AdventureWorks2016.Person.Person person\n        ON Employee.BusinessEntityID = Person.BusinessEntityID\n  GO<\/pre>\n\n\n\n
We can now create and fill our new staff table and the StaffClosure Table<\/p>\n\n\n\n
  --clear out existing version if it exists\n  IF Object_Id('StaffClosure', 'U') IS NOT null DROP TABLE StaffClosure;\n  GO\n  IF Object_Id('Staff', 'U') IS NOT null DROP TABLE Staff;\n  GO\n  --create our staff hierarchy table\n  CREATE TABLE Staff --these represent the nodes with whatever attributes \n    (\n    Employee_ID INT IDENTITY PRIMARY key,--for inserts etc.\n    --a real table would have more attributes\n    employee NVARCHAR(80),\n    FirstName NVARCHAR(40),\n    LastName NVARCHAR(40),\n    );\n  SET IDENTITY_INSERT Staff ON --so we can put existing data in the table\n  --now stock out nodes table with our mock data\n  INSERT INTO Staff\n  (Employee_ID,  Employee,\n   FirstName, LastName)\n  SELECT Employee_ID, employee, FirstName, LastName FROM #staff\n  -- so we can add people\n  SET IDENTITY_INSERT Staff OFF<\/pre>\n\n\n\n
The Closure table has a constraint to prevent duplicate edges and to ensure that all heads and tails reference the IDs of existing staff. We\u2019ve added a Depth<\/strong> attribute that isn\u2019t strictly necessary but it\u2019s useful. As well as the direct parent\/child references (edges) between nodes, there are ancestor\/descendent references (edges). Also, nodes reference themselves (reflexive closure).<\/p>\n\n\n\n
  CREATE TABLE StaffClosure --where we store our edges\n    (\n    ancestor INT NOT NULL,\n    descendant INT NOT NULL,\n    Depth INT NOT NULL --this is handy to have but not essential\n    PRIMARY KEY (ancestor, descendant),--enforce the rules\n    FOREIGN KEY (ancestor) REFERENCES Staff (employee_ID),\n    FOREIGN KEY (descendant) REFERENCES Staff (employee_ID)\n    );\n  INSERT INTO StaffClosure(Ancestor, Descendant, depth)\n  SELECT Employee_ID AS Ancestor,Employee_ID AS descendent, 0 FROM #staff\n  --add reflexive closure\n  UNION ALL\n  --every edge as already defined within the path of the hierarchyid. \n  SELECT parent.Employee_ID, child.Employee_ID AS child,f.depth\n   FROM   (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(10),(11),(12))f(depth)\n   --if you are likely to have a greater depth, then add numbers!!\n  inner join\n   #Staff AS child\n   ON  child.ancestors >= f.depth\n      INNER JOIN #Staff AS parent\n        ON parent.OrganizationNode = child.OrganizationNode.GetAncestor(depth)<\/pre>\n\n\n\n
That\u2019s it. All done now. We have a hierarchy represented by a closure table that we can play with now.<\/p>\n\n\n\n
Listing the Tree<\/h3>\n\n\n\n
Let\u2019s start by seeing who is there and the chain of reporting in the organisation. We\u2019ll include the whole organisation by specifying that the CEO is in the chain. Obviously. You wouldn\u2019t hard-code the name of the CEO in a working system, but use a function to return the reporting structure of the part of the organisation you\u2019re interested in.<\/p>\n\n\n\n
Non-recursive Listing<\/h4>\n\n\n\n
  SELECT \n    STUFF(( SELECT '->' + n.FirstName+ ' '+n.LastName\n            from Staffclosure down\n  \t\t\tINNER JOIN Staffclosure up on up.descendant = down.descendant \n  \t\t\tINNER JOIN Staff n on n.employee_id = up.ancestor\n  \t\t  WHERE down.ancestor = d.ancestor\n  \t\t  and down.descendant <>  down.ancestor\n  \t\t  AND down.descendant=d.descendant ORDER BY up.depth desc\n            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS [path]\n  FROM Staffclosure d\n    WHERE d.ancestor = \n  (\n  \tSELECT employee_id FROM staff \n  \tWHERE employee LIKE 'Ken J S\u00e1nchez. Chief Executive Officer')\n    AND d.descendant <>  d.ancestor\n  ORDER BY path<\/pre>\n\n\n\n
And we can see everyone listed with their management chain.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
This method uses a simple aggregation to do this which relies on sorting the paths to get the order right. This was demonstrated by Bill Karwin in Rendering Trees with Closure Tables<\/a>. To do this using SQL Server, we used the XML trick to emulate the group_concat()<\/strong> that MySQL has.<\/p>\n\n\n\n
With SQL Server 2017, we now have String_agg()<\/strong> that is close to group_concat()<\/strong> but which doesn\u2019t allow you to specify the order of concatenation of the expressions within the strings .<\/p>\n\n\n\n
In this example, the ultimate boss (ancestor) was specified by hard-coding the name of the person. If this were a Table-valued function you could get the complete reporting line of any node and its descendants all the way up to the ultimate boss..<\/p>\n\n\n\n
  IF Object_Id('dbo.ReportingLine') IS NOT NULL DROP FUNCTION dbo.ReportingLine\n  GO\n  CREATE FUNCTION dbo.ReportingLine\n    \/**\n  Summary: >\n    List the reporting line for each employee\n    ordered by the reporting path\n  Author: Phil Factor\n  Date: 03\/04\/2018\n  Database: PhilFactor\n  Examples:\n     - Select * from MyStaff cross apply dbo.Hierarchy(HeadOfDepartment)\n  Returns: >\n    A table indicating the reporting line as a string\n          **\/\n    (@employee_id INT)\n  RETURNS TABLE\n  --WITH ENCRYPTION|SCHEMABINDING, ..\n  AS\n  RETURN\n    (\n    SELECT Stuff\n      (\n        (SELECT '->' + n.FirstName + ' ' + n.LastName\n  \t\t\tFROM dbo.StaffClosure AS down\n  \t\t\tINNER JOIN dbo.StaffClosure AS up\n  \t\t\t\tON up.descendant = down.descendant\n  \t\t\tINNER JOIN dbo.Staff AS n\n  \t\t\t\tON n.Employee_ID = up.ancestor\n  \t\t\tWHERE down.ancestor = d.ancestor\n  \t\t\tAND down.descendant <> down.ancestor\n  \t\t\tAND down.descendant = d.descendant\n  \t\t\tORDER BY up.Depth DESC\n  \t\tFOR XML PATH(''), TYPE\n  \t\t).value('.', 'NVARCHAR(MAX)'),\n  \t    1,2,'') AS path\n      FROM dbo.StaffClosure AS d\n      WHERE d.ancestor = @employee_id AND d.descendant <> d.ancestor\n    );\n  GO<\/pre>\n\n\n\n
And here is an example of its use<\/p>\n\n\n\n
  DECLARE @Employee_ID INT =\n            (\n            SELECT Staff.Employee_ID\n              FROM dbo.Staff\n              WHERE Staff.employee LIKE 'Mr. Brian S Welcker. Vice President of Sales'\n            )\n  SELECT * FROM dbo.ReportingLine(@Employee_ID) ORDER BY ReportingLine.path<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Recursive Listing<\/h4>\n\n\n\n
The same thing can be done using a recursive Common Table Expression (CTE), with the advantage that the natural order reflects the traversal of the nodes of the hierarchy and so you don\u2019t need to order by the path.<\/p>\n\n\n\n
  ;WITH TransClosedEdges (Path, descendant, Ancestor, depth, recursion)\n  AS (SELECT \n        Convert(NVARCHAR(MAX), a.firstname+' '+a.lastname + ' ->' \n           + d.firstname+' '+d.lastname),\n        StaffClosure.descendant, StaffClosure.ancestor, depth, 1\n        FROM dbo.StaffClosure\n          INNER JOIN dbo.Staff AS a\n            ON a.Employee_ID = StaffClosure.ancestor\n          INNER JOIN dbo.Staff AS d\n            ON d.Employee_ID = StaffClosure.descendant\n        WHERE StaffClosure.descendant <> StaffClosure.ancestor\n          AND StaffClosure.ancestor = (\n  \t\t    SELECT employee_id FROM staff \n  \t\t    WHERE employee LIKE 'Ken J S\u00e1nchez. Chief Executive Officer')\n          AND depth = 1\n      UNION ALL\n      SELECT ee.Path + ' ->' + Staff.firstname+' '+Staff.lastname,\n  \t   e.descendant, e.ancestor, e.depth, ee.recursion + 1\n        FROM dbo.StaffClosure AS e\n          INNER JOIN TransClosedEdges AS ee\n            ON e.ancestor = ee.descendant\n          INNER JOIN dbo.Staff\n            ON Staff.Employee_ID = e.descendant\n        WHERE e.descendant <> e.ancestor AND e.depth = 1)\n    SELECT path FROM TransClosedEdges;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
In case you wish to preserve the order of the result, you can provide a row count and then subsequently sort by that row count, by adding \u2026<\/p>\n\n\n\n
  SELECT row_number() OVER (ORDER BY (SELECT NULL)), path FROM TransClosedEdges;<\/pre>\n\n\n\n
\u2026 in the final line<\/p>\n\n\n\n
Both these queries allow you report the management structure starting at whatever point you wish. The Recursive CTE was suggested by Vadim Tropashko, in his book SQL Design Patterns (Rampant Techpress, Kittrell, NC, USA, 2006.). Here is another version that is handy for a simpler indented list of the structure. This version can be easily turned into a YAML document and thence into JSON.<\/p>\n\n\n\n
  ;WITH TransClosedEdges (name, TheOrder, descendant, Ancestor, recursion)\n  AS (SELECT Convert(NVARCHAR(MAX), a.employee),0,\n        StaffClosure.descendant, StaffClosure.ancestor, 1\n        FROM dbo.StaffClosure\n          INNER JOIN dbo.Staff AS a\n            ON a.Employee_ID = StaffClosure.ancestor\n        WHERE StaffClosure.descendant = StaffClosure.ancestor\n          AND employee LIKE 'Ken J S\u00e1nchez. Chief Executive Officer'\n      UNION ALL\n  SELECT Convert(NVARCHAR(MAX), staff.employee),Convert(INT,Row_Number() OVER (ORDER BY (SELECT NULL))),\n   e.descendant, e.Ancestor,  recursion+1\n   FROM \n  dbo.staffClosure AS e INNER JOIN TransClosedEdges ee\n  ON ee.descendant=e.ancestor AND e.depth=1\n  INNER JOIN staff ON Staff.Employee_ID = e.descendant)\n    SELECT space(recursion*4)+name FROM TransClosedEdges ORDER BY TheOrder;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Here, I\u2019ve used the employee\u2019s job title too, which is handy for double-checking that everyone is reporting to the right manager!<\/p>\n\n\n\n
Adding an Employee<\/h3>\n\n\n\n
Let\u2019s create a new employee, Philip J Factor IIIrd.<\/p>\n\n\n\n
  INSERT INTO Staff\n    ( employee, FirstName,Lastname)\n  SELECT \n   'Philip J Factor IIIrd. Tool Designer','Philip','Factor'<\/pre>\n\n\n\n
Now, we\u2019d like him to report to Ovidiu V Cracium. Senior Tool Designer. We need to insert a new leaf node, including the self-referencing edge. Then we take Ovidiu V Cracium\u2019s connections and copy them to Philip but adding 1 to the depth. Then all we need is the edge that links Ovidiu to Philip.<\/p>\n\n\n\n
  DECLARE @boss INT =\n      (\n      SELECT Staff.Employee_ID\n      FROM dbo.Staff\n      WHERE Staff.employee LIKE 'Ovidiu V Cracium. Senior Tool Designer'\n      );\n  DECLARE @Report INT =\n      (\n      SELECT Staff.Employee_ID\n      FROM dbo.Staff\n      WHERE Staff.employee LIKE 'Philip J Factor IIIrd. Tool Designer'\n      );\n  INSERT INTO dbo.StaffClosure (ancestor, descendant, depth)\n  VALUES\n    (@Report, @Report, 0),\n    (@boss, @Report, 1);\n  INSERT INTO dbo.StaffClosure (ancestor, descendant, depth)\n    SELECT ancestor, @Report, depth + 1\n      FROM dbo.StaffClosure\n      WHERE StaffClosure.descendant = @boss AND ancestor <> descendant;<\/pre>\n\n\n\n
Now we can check that he is there OK (Normally, you\u2019d turn this code into a function but I\u2019ve unwrapped it, so you can see the cogs move)<\/p>\n\n\n\n
  ;WITH TransClosedEdges (name, TheOrder, descendant, Ancestor, recursion)\n  AS (SELECT Convert(NVARCHAR(MAX), a.employee),0,\n        StaffClosure.descendant, StaffClosure.ancestor, 1\n        FROM dbo.StaffClosure\n          INNER JOIN dbo.Staff AS a\n            ON a.Employee_ID = StaffClosure.ancestor\n        WHERE StaffClosure.descendant = StaffClosure.ancestor\n          AND employee LIKE 'Ovidiu V Cracium. Senior Tool Designer'\n      UNION ALL\n  SELECT Convert(NVARCHAR(MAX), staff.employee),Convert(INT,Row_Number() OVER (ORDER BY (SELECT NULL))),\n   e.descendant, e.Ancestor,  recursion+1\n   FROM \n  dbo.staffClosure AS e INNER JOIN TransClosedEdges ee\n  ON ee.descendant=e.ancestor AND e.depth=1\n  INNER JOIN staff ON Staff.Employee_ID = e.descendant)\n    SELECT space(recursion*4) + name FROM TransClosedEdges ORDER BY TheOrder;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
We can also use our table-valued function to check the reporting line<\/p>\n\n\n\n
  DECLARE @Employee_ID INT =\n            (\n            SELECT Staff.Employee_ID\n              FROM dbo.Staff\n              WHERE Staff.employee LIKE 'Ovidiu V Cracium. Senior Tool Designer'\n            )\n  SELECT * FROM dbo.ReportingLine(@Employee_ID) ORDER BY ReportingLine.path<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Record That an Employee Has Left<\/h3>\n\n\n\n
Now let\u2019s imagine that Roberto Tamburello, the Engineering Manager, has left the company. We can\u2019t just delete him from the staff. First, we need to decide who his team should now report to. Terri Lee Duffy, Vice President of Engineering, isn\u2019t doing that much, so let\u2019s transfer Roberto\u2019s team to him. In a sense, it is a bit of a promotion for the team because they report directly to a manager who is higher up in the pecking order. Depths will be affected, and so on. Our routine needs to be robust enough to transfer the leaderless team anywhere else in the organization: not just up and down the same branch.<\/p>\n\n\n\n
Here is the reporting line for Terri Duffy before we do anything<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
First, we get the IDs of the participants<\/p>\n\n\n\n
  DECLARE @AddToReports INT =\n      (\n      SELECT Staff.Employee_ID\n      FROM dbo.Staff\n      WHERE Staff.employee LIKE 'Terri Lee Duffy. Vice President of Engineering'\n      );\n  DECLARE @remove INT =\n      (\n      SELECT Staff.Employee_ID\n      FROM dbo.Staff\n      WHERE Staff.employee LIKE 'Roberto Tamburello. Engineering Manager'\n      );\n  \/* create a table that contains the altered branch *\/\n  DECLARE @BranchClosure TABLE (ancestor INT NOT NULL, descendant INT NOT NULL, Depth INT NOT NULL)\n  INSERT INTO @BranchClosure (ancestor, descendant, Depth)\n  --GET the relationships of the reports to @remove\n  -- up to the level of the removal of the node, but at the new depth\n  SELECT StaffClosure.ancestor, StaffClosure.descendant,\n    StaffClosure.Depth-- the existing depth \n    FROM dbo.StaffClosure\n    WHERE StaffClosure.ancestor = @remove \n    AND StaffClosure.Depth > 0\n  UNION ALL\n  --get the interrelationships of the reports within the branch\n  SELECT StaffClosure.ancestor, StaffClosure.descendant, StaffClosure.Depth \n     --these stay at the existing depth \n    FROM dbo.StaffClosure\n    WHERE StaffClosure.ancestor IN\n            (\n            SELECT descendant --the existing depth \n              FROM dbo.StaffClosure\n              WHERE ancestor = @remove AND Depth > 0\n            )\n  UNION ALL\n  --the relationships above the new boss\n  SELECT f.ancestor, g.descendant, g.Depth + f.Depth AS depth\n    FROM\n      (---all the new relationships from the branch to the new part of the tree\n      SELECT StaffClosure.ancestor, StaffClosure.descendant, StaffClosure.Depth\n        FROM dbo.StaffClosure\n        WHERE StaffClosure.descendant = @AddToReports AND StaffClosure.Depth > 0\n      ) AS f\n      CROSS JOIN --for every leaf in the branch we give them the edge to the trunk\n        (\n        SELECT StaffClosure.descendant, StaffClosure.Depth --the existing depth \n          FROM dbo.StaffClosure\n          WHERE StaffClosure.ancestor = @remove AND StaffClosure.Depth > 0\n        ) AS g\n  BEGIN TRANSACTION\n  \/* delete any existing versions of the relationships *\/\n  DELETE FROM dbo.StaffClosure\n    FROM dbo.StaffClosure AS sc\n      INNER JOIN @BranchClosure AS BC\n        ON BC.descendant = sc.descendant\n  \/* first update the old manager to the new*\/\n  UPDATE @BranchClosure\n    SET [@BranchClosure].ancestor = @AddToReports\n    WHERE [@BranchClosure].ancestor = @remove\n  \/* and insert the new relationships *\/\n  INSERT INTO dbo.StaffClosure (ancestor, descendant, Depth)\n    SELECT [@BranchClosure].ancestor, [@BranchClosure].descendant,\n      [@BranchClosure].Depth\n      FROM @BranchClosure\n  \/* mop up any residual *\/\n  DELETE FROM dbo.StaffClosure\n    WHERE StaffClosure.ancestor = @remove OR StaffClosure.descendant = @remove;\n  COMMIT TRANSACTION;<\/pre>\n\n\n\n
Now here is Terri Duffy\u2019s team<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
General Reporting<\/h3>\n\n\n\n
These tend to be very simple to do, owing to the comprehensive way that the edges are placed in the closure table.<\/p>\n\n\n\n
How many direct reports does ‘Stephen Y Jiang. North American Sales Manager’ have?<\/p>\n\n\n\n
  SELECT count(*)\n  FROM Staffclosure d\n  INNER JOIN staff dstaff ON d.descendant=dstaff.employee_id\n  INNER JOIN staff boss ON d.ancestor= boss.employee_ID\n  WHERE boss.employee =  'Stephen Y Jiang. North American Sales Manager'\n   and d.descendant <>  d.ancestor\n  AND depth=1<\/pre>\n\n\n\n
How many indirect reports does ‘Ken J S\u00e1nchez. Chief Executive Officer’ have?<\/p>\n\n\n\n
  SELECT Count(*)\n   FROM Staffclosure d\n  INNER JOIN staff dstaff ON d.descendant=dstaff.employee_id\n  INNER JOIN staff boss ON d.ancestor= boss.employee_ID\n  WHERE boss.employee =  'Ken J S\u00e1nchez. Chief Executive Officer'\n   and d.descendant <>  d.ancestor\n  AND depth<>1<\/pre>\n\n\n\n
Which of AdventureWorks staff have the most direct reports?<\/p>\n\n\n\n
  SELECT TOP 100 boss.employee, Count(*)\n  FROM Staffclosure d\n  INNER JOIN staff dstaff ON d.descendant=dstaff.employee_id\n  INNER JOIN staff boss ON d.ancestor= boss.employee_ID\n   and d.descendant <>  d.ancestor\n  AND depth=1\n  GROUP BY  boss.employee\n  ORDER BY Count(*) desc<\/pre>\n\n\n\n
Using Recursive Queries<\/h3>\n\n\n\n
There is an occasional need for a recursive function. Getting a JSON document for an organisation hierarchy is an example of where it is handy.<\/p>\n\n\n\n
  IF Object_Id('dbo.JSONStaffReportingTo', 'FN') IS NOT null\n    DROP FUNCTION dbo.JSONStaffReportingTo;\n  GO\n  CREATE FUNCTION JSONStaffReportingTo (@Ancestor INT)\n  RETURNS NVARCHAR(MAX)\n    BEGIN\n      RETURN\n        (SELECT \n  \t   Coalesce(\n  \t     (SELECT '{\"' + String_Escape(Staff.employee, 'json') + '\": ['\n                    + Stuff( --get a list of reports\n                        (SELECT ',' + dbo.JSONStaffReportingTo(StaffClosure.descendant)\n                           FROM dbo.StaffClosure\n                             WHERE @Ancestor = StaffClosure.ancestor\n                               AND depth = 1\n                          FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'') + ']}'),\n            '\"' + String_Escape(Staff.employee, 'json') + '\"' )AS reports\n          FROM dbo.Staff\n          WHERE Staff.Employee_ID = @Ancestor\n        );\n    END;\n  GO\n  SELECT dbo.JSONStaffReportingTo(Staff.Employee_ID)\n    FROM dbo.Staff\n    WHERE Staff.employee LIKE 'Stephen Y Jiang. North American Sales Manager';<\/pre>\n\n\n\n
Which gives (after formatting) \u2026<\/p>\n\n\n\n
{  \n   \"Stephen Y Jiang. North American Sales Manager\":[  \n      \"Michael G Blythe. Sales Representative\",\n      \"Linda C Mitchell. Sales Representative\",\n      \"Jillian Carson. Sales Representative\",\n      \"Garrett R Vargas. Sales Representative\",\n      \"Tsvi Michael Reiter. Sales Representative\",\n      \"Pamela O Ansman-Wolfe. Sales Representative\",\n      \"Shu K Ito. Sales Representative\",\n      \"Jos\u00e9 Edvaldo Saraiva. Sales Representative\",\n      \"David R Campbell. Sales Representative\",\n      \"Mr. Tete A Mensa-Annan. Sales Representative\"\n  ]\n}\n<\/pre>\n\n\n\n
Read also:<\/strong> Window functions for hierarchical queries<\/a><\/p>\n\n\n\n
Extending the Model<\/h2>\n\n\n\n
The whole point of the closure pattern is that we can give the edges other attributes. We\u2019ve actually done this in the previous example with the depth attribute. We\u2019ll add another, the time the edge started and ended.<\/p>\n\n\n\n
Imagine we have a playground with some children. Fortunately, they all have different names, so we can use the name as a primary key to make life simpler.<\/p>\n\n\n\n
  CREATE TABLE Participants (name NVARCHAR(10) PRIMARY KEY)\n  INSERT INTO Participants (name)\n  VALUES\n    ('Mary'),  ('Atilla'),  ('Gengis'),  ('Catherine'),\n    ('Vlad'),  ('lucrezia'),  ('Nell'),  ('Adolf')<\/pre>\n\n\n\n
We now create the closure table<\/p>\n\n\n\n
  CREATE TABLE TeamClosure --where we store our edges\n    (\n    ancestor NVARCHAR(10) NOT NULL,\n    descendant NVARCHAR(10) NOT NULL,\n    Depth INT NOT NULL, --this is handy to have but not essential\n    StartTime DATETIME NOT NULL,\n    FinishTime DATETIME null,\n    PRIMARY KEY (ancestor, descendant),--enforce the rules\n    FOREIGN KEY (ancestor) REFERENCES Participants (name),\n    FOREIGN KEY (descendant) REFERENCES Participants (name)\n    );<\/pre>\n\n\n\n
We can now simply add the reflexive closure<\/p>\n\n\n\n
  INSERT INTO TeamClosure(Ancestor, Descendant, depth, StartTime)\n  SELECT name AS Ancestor,name AS descendent, 0, '4\/4\/2018 12:00'FROM Participants\n  This time, we add the entire story into the closure table\n  INSERT INTO TeamClosure(Ancestor, Descendant, depth, StartTime) values\n  ('Lucrezia','Mary',1, '4\/4\/2018 12:20'), --first Mary joined Lucrezia's gang\n  ('Lucrezia','Atilla',1, '4\/4\/2018 12:22'), --two minutes later came Atilla\n  ('Lucrezia','Vlad',1, '4\/4\/2018 12:30'), --eight minutes later came Vlad\n  ('Lucrezia','Adolf',1, '4\/4\/2018 12:30'), --along with Adolf\n  --then Gengis and Nell joined Adolf\u2019s subteam, Gengis, then nell\n  ('Lucrezia','Gengis',2,'4\/4\/2018 12:40'), -- it's Lucrezia's gang\n  ('Adolf','Gengis',1,'4\/4\/2018 12:40'), -- but he is with Adolf's group\n  ('Lucrezia','Nell',2,'4\/4\/2018 12:42'), -- it's Lucrezia's gang for her too\n  ('Adolf','Nell',1,'4\/4\/2018 12:42'), -- but she is with Adolf's group\n  --then Catherine joined Vlad\u2019s subteam \n  ('Lucrezia','Catherine',2,'4\/4\/2018 12:45'), -- it's Lucrezia's gang\n  ('Vlad','Catherine',1,'4\/4\/2018 12:48') -- but she is with Vlad's group\n  UPDATE TeamClosure\n    SET TeamClosure.FinishTime = '4\/4\/2018 13:40'\n    WHERE TeamClosure.descendant = 'Nell' --then Nell goes\n  UPDATE TeamClosure\n    SET TeamClosure.FinishTime = '4\/4\/2018 13:52'\n    WHERE TeamClosure.descendant = 'Gengis' --then Gengis goes<\/pre>\n\n\n\n
We can now display the organization tree at any time.<\/p>\n\n\n\n
  IF Object_Id('dbo.GangStructure') IS NOT NULL DROP FUNCTION dbo.GangStructure\n  GO\n  CREATE FUNCTION dbo.GangStructure\n    \/**\n  Summary: >\n    Show the reporting chain\n    of lucrezia's gang at any time\n  Author: Phil Factor\n  Date: 04\/04\/2018\n  Database: PhilFactor\n  Examples:\n     - Select * from dbo.GangStructure('4\/4\/2018 12:42')\n  Returns: >\n    A table showing the gang structure\n          **\/\n    (@ThePointInTime DATETIME)\n  RETURNS TABLE\n  --WITH ENCRYPTION|SCHEMABINDING, ..\n  AS\n  RETURN\n    (\n    WITH TransClosedEdges (Path, Ancestor, descendant, depth, recursion)\n    AS (SELECT \n          Convert(NVARCHAR(MAX),TeamClosure.ancestor + '->' + TeamClosure.descendant),\n  \t\tTeamClosure.ancestor, TeamClosure.descendant,\n          TeamClosure.Depth, 1\n          FROM dbo.TeamClosure\n          WHERE TeamClosure.descendant <> TeamClosure.ancestor\n            AND TeamClosure.ancestor = 'Lucrezia'\n            AND TeamClosure.Depth = 1\n            AND @ThePointInTime \n  \t\t   BETWEEN TeamClosure.StartTime AND Coalesce(TeamClosure.FinishTime,GetDate())\n        UNION ALL\n        SELECT ee.Path + '->' + e.descendant, e.ancestor, e.descendant, e.Depth,\n          ee.recursion + 1\n          FROM dbo.TeamClosure AS e\n            INNER JOIN TransClosedEdges AS ee\n              ON e.ancestor = ee.descendant\n          WHERE e.descendant <> e.ancestor\n            AND e.Depth = 1\n            AND @ThePointInTime \n  \t\t    BETWEEN e.StartTime AND Coalesce(e.FinishTime,GetDate()))\n    SELECT TransClosedEdges.Path FROM TransClosedEdges\n    )\n  GO\n    Select * from dbo.GangStructure('4\/4\/2018 12:30') <\/pre>\n\n\n\n
This is at the point that Adolf, Atilla, Mary and Vlad had joined the gang but Gengis and Neil hadn\u2019t appeared<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
  Select * from dbo.GangStructure('4\/4\/2018 12:42')<\/pre>\n\n\n\n
Now Neil and Gengis have joined Adolf\u2019s faction<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
  Select * from dbo.GangStructure('4\/4\/2018 13:00')<\/pre>\n\n\n\n
Catherine has turned up to be Vlad\u2019s sidekick<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
  Select * from dbo.GangStructure('4\/4\/2018 14:00')<\/pre>\n\n\n\n
Now Neil and Gengis have both flounced off indoors<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
OK, at this stage it looks like overkill, but this system is capable of a great deal of elaboration at little cost.<\/p>\n\n\n\n
Conclusions<\/h2>\n\n\n\n
There are two main points about hierarchies and trees for representing such data as parts lists, organizations or routes.<\/p>\n\n\n
\n