HierarchyID Data Type in SQL Server 2008
MS SQL Server 2008 new feature, the HierarchyID data type.
Introduction
One of the fantastic new features of SQL Server 2008 is the data type HierarchyID
.
The HierarchyID
data type allows you to construct relationships among data elements within a table, specifically to represent a position in a hierarchy.
Facts about the HierarchyId data type
Here are a few facts that you must know before starting to use HierarchyID
:
- New system CLR type supporting trees.
- Internally stored as
varbinary
<= 900 bytes. - Holds a path that provides a topological sort of a tree.
- Has a set of methods that provide tree functionality:
GetAncestor
,GetDescendant
,GetLevel
,GetRoot
,IsDescendant
,Parse
,Read
,Reparent
,ToString
,Write
. - Efficient querying that can utilize depth-first and breadth-first indexes.
Methods provided are as follows:
GetAncestor
: Returns aHierarchyID
that represents the nth ancestor of thisHierarchyID
node.GetDescendant
: Returns a child node of thisHierarchyID
node.GetLevel
: Returns an integer that represents the depth of thisHierarchyID
node in the overall hierarchy.GetRoot
: Returns the rootHierarchyID
node of this hierarchy tree. Static.IsDescendant
: Returns true if the passed-in child node is a descendant of thisHierarchyID
node.Parse
: Converts a string representation of a hierarchy to aHierarchyID
value. Static.Reparent
: Moves a node of a hierarchy to a new location within the hierarchy.ToString
: Returns a string that contains the logical representation of thisHierarchyID
.
Example
CREATE TABLE dbo.Employees
(
empid INT NOT NULL,
hid HIERARCHYID NOT NULL,
lvl AS hid.GetLevel() PERSISTED,
empname VARCHAR(25) NOT NULL,
salary MONEY NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX idx_depth_first
ON dbo.Employees(hid);
CREATE UNIQUE INDEX idx_breadth_first
ON dbo.Employees(lvl, hid);
CREATE UNIQUE INDEX idx_empid
ON dbo.Employees(empid);
In the above code snippet, I have created a table using HierarchyID
as a data type.
CREATE PROC dbo.usp_AddEmp
@empid AS INT, @mgrid AS INT = NULL,
@empname AS VARCHAR(25), @salary AS MONEY
AS
DECLARE @hid AS HIERARCHYID, @mgr_hid AS HIERARCHYID,
@last_child_hid AS HIERARCHYID;
IF @mgrid IS NULL
SET @hid = HIERARCHYID::GetRoot();
ELSE
BEGIN
SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);
SET @last_child_hid =
(SELECT MAX(hid) FROM dbo.Employees
WHERE hid.GetAncestor(1) = @mgr_hid);
SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);
END
INSERT INTO dbo.Employees(empid, hid, empname, salary)
VALUES(@empid, @hid, @empname, @salary);
In the above code snippet, I have created a Stored Procedure that can be used to insert data into the table with HierarchyID
as data type in one of the columns.
-- Subtree
SELECT C.*
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON P.empid = 3
AND P.hid.IsDescendant(C.hid) = 1;
-- Path
SELECT P.*
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON C.empid = 14
AND P.hid.IsDescendant(C.hid) = 1;
-- Presentation/sorting
SELECT
REPLICATE(' | ', lvl) + empname AS empname, hid.ToString() AS path
FROM dbo.Employees
ORDER BY hid;
The above code snippet shows how to query the table with a HierarchyID
column.
Conclusion
So now, with the HierarchyID
data type, we can generate trees with parent-child relationships with more structured code.
Hope you enjoyed this article. Happy programming!!!