Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Using the hierarchyid SQL Server Data Type

Mark Lee

We have a need to store sales data for a company. In this company sales are measured by territories, which are represented by offices. Offices have a hierarchy which forms the territories and we need to be able to represent this hierarchy in our data model in order to correctly report sales data.

Let’s take the example of a requirement to create a reference table to store data about offices in our company with a hierarchical structure. One approach is to create a referencing field in a table to reference a parent record in the same table. However, managing hierarchical data in a relational database can be very maintenance intensive and require custom code to manipulate the data especially dealing with their relationship within the hierarchy.

The hierarchyid data type enables the creation of a hierarchical data in a relation database with significantly less maintenance overhead.

http://technet.microsoft.com/en-us/library/bb677173.aspx

Some of the advantages of hierarchyid data type:

  • Path of the node is encoded into the id
  • Methods can be used to arbitrarily add, update and delete node at any point within the structure.

Creating the Table

First, we will create an example table to store our office data.

CREATE TABLE Offices

(

NodeId                     hierarchyid PRIMARY KEY CLUSTERED,

NodeLevel               AS NodeId.GetLevel(),

OfficeId                    int UNIQUE NOT NULL,

OfficeName              NVARCHAR(50) NOT NULL,

QuarterlySales          DECIMAL(15,2) NOT NULL

)

Populating the Table

Now that we have created that table, we will populate it with data.

Creating the root node

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(hierarchyid :: GetRoot(), 1, ‘Head Office’, 0);

We will give the Head Office “0” in sales because it does not do any direct sales.

Populating the children nodes

To populate a child node, we need derive the NodeId for the new record. To do this we will use the GetDescendant method.

The GetDescendant method is called on a hierarchyid data type and takes two parameters.

The two parameters represent the ordinal position of the node. The first parameter indicates the node that precedes it and the second parameter represents the node that will follow it.

So to generate a NodeId value and then create a new record for a child record we use the following code.

DECLARE @ParentNodeId hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 1

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(@ParentNodeId.GetDescendant(NULL, NULL) , 2, ‘Western US Office’, 0);

First we determine the NodeId of the parent and then use the GetDescendant method to generate a NodeId for the child record.

With this approach we are required to manually specify the ordinal position to insert the child record. However, we can automate this by using the GetAncestor method.

The GetAncestor method takes one argument, an integer value the specifies how many level up the hierarchy to return. We can use this to get all the children a parent and determine where to add the next child.

DECLARE @ParentNodeId         hierarchyid

DECLARE @LastChildNodeId     hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 1

SELECT @LastChildNodeId = MAX(NodeId)

FROM Offices

WHERE NodeId.GetAncestor(1) = @ParentNodeId

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(@ParentNodeId.GetDescendant(@LastChildNodeId, NULL) , 3, ‘Eastern US Office’, 0);

This logic can be packaged into a stored procedure or function simply the creation of new records in the hierarchy.

Let’s go ahead and create some additional offices and populate them with sales data.

DECLARE @ParentNodeId         hierarchyid

DECLARE @LastChildNodeId     hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 2

SELECT @LastChildNodeId = MAX(NodeId)

FROM Offices

WHERE NodeId.GetAncestor(1) = @ParentNodeId

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(@ParentNodeId.GetDescendant(@LastChildNodeId, NULL) , 4, ‘San Francisco Office’, 100000);

DECLARE @ParentNodeId         hierarchyid

DECLARE @LastChildNodeId     hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 2

SELECT @LastChildNodeId = MAX(NodeId)

FROM Offices

WHERE NodeId.GetAncestor(1) = @ParentNodeId

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(@ParentNodeId.GetDescendant(@LastChildNodeId, NULL) , 5, ‘San Diego Office’, 10000);

DECLARE @ParentNodeId         hierarchyid

DECLARE @LastChildNodeId     hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 3

SELECT @LastChildNodeId = MAX(NodeId)

FROM Offices

WHERE NodeId.GetAncestor(1) = @ParentNodeId

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(@ParentNodeId.GetDescendant(@LastChildNodeId, NULL) , 6, ‘New York Office’, 110000);

DECLARE @ParentNodeId         hierarchyid

DECLARE @LastChildNodeId     hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 3

SELECT @LastChildNodeId = MAX(NodeId)

FROM Offices

WHERE NodeId.GetAncestor(1) = @ParentNodeId

INSERT INTO Offices

( NodeId, OfficeId, OfficeName, QuarterlySales)

VALUES

(@ParentNodeId.GetDescendant(@LastChildNodeId, NULL) , 7, ‘Pittsburgh Office’, 100000);

Query the contents of the table:

SELECT NodeId.ToString() Path, *

From Offices

Results:


Querying the Table

Now that we have populated the table we want to be able to easily query and display the hierarchy. Traditionally, we would have used a recursive function or a common table expression to do this.

However, using a hierarchyid data type we have methods available to us that simplifies the traversal of the tree hierarchy. We have already discussed the GetAncestor method, now we will go in the other direction and get the descendants of a node.

To do this we will leverage the IsDescendantOf method. This method takes in a hierarchyid and evaluates true or false. Using this method we can get all the child, grandchildren, and so on of a node in the hierarchy and calculate the sales by an office

DECLARE @ParentNodeId         hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 1

SELECT SUM(QuarterlySales)

FROM Offices

WHERE NodeId.IsDescendantOf(@ParentNodeId) = 1

Result:

320000.00

DECLARE @ParentNodeId         hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 2

SELECT SUM(QuarterlySales)

FROM Offices

WHERE NodeId.IsDescendantOf(@ParentNodeId) = 1

Result:

110000.00

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 2

SELECT SUM(QuarterlySales)

FROM Offices

WHERE NodeId.IsDescendantOf(@ParentNodeId) = 1

Result:

210000.00

One last query we can do is to display all the rolled up sales for each office and their children:

SELECT

O.OfficeId,

O.OfficeName,

(

Select SUM(QuarterlySales)

FROM Offices

WHERE NodeId.IsDescendantOf(O.NodeId) = 1

) AS ‘Total Office Sales’

FROM

(

SELECT

NodeId,

NodeLevel,

OfficeId,

OfficeName,

QuarterlySales

FROM Offices

) AS

 

Results:


Re-parenting a Node

The final maintenance operation typically done within a hierarchy is re-parenting a child node. To do this we will use the GetReparentedValue method.

The GetReparentedValue method takes in two parameters. The first is the original parent hierarchyid, and the second is the new parent.

Ex.

DECLARE @ParentNodeId         hierarchyid

DECLARE @NewParentNodeId  hierarchyid

DECLARE @ChildNodeId           hierarchyid

SELECT @ParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 2

SELECT @NewParentNodeId = NodeId

FROM Offices

WHERE OfficeId = 4

SELECT @ChildNodeId = NodeId

FROM Offices

WHERE OfficeId = 5

UPDATE Offices

SET NodeId = @ChildNodeId.GetReparentedValue(@ParentNodeId, @NewParentNodeId)

WHERE NodeId = @ChildNodeId

Now if we query table:

SELECT NodeId.ToString() Path, *

From Offices

Results:


The path now shows that the San Diego Office now reports to the San Francisco Office.

Related Articles

No comments

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\