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 a HierarchyID
that represents the nth ancestor of this HierarchyID
node.GetDescendant
: Returns a child node of this HierarchyID
node.GetLevel
: Returns an integer that represents the depth of this HierarchyID
node in the overall hierarchy.GetRoot
: Returns the root HierarchyID
node of this hierarchy tree. Static.IsDescendant
: Returns true if the passed-in child node is a descendant of this HierarchyID
node.Parse
: Converts a string representation of a hierarchy to a HierarchyID
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 this HierarchyID
.
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.
SELECT C.*
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON P.empid = 3
AND P.hid.IsDescendant(C.hid) = 1;
SELECT P.*
FROM dbo.Employees AS P
JOIN dbo.Employees AS C
ON C.empid = 14
AND P.hid.IsDescendant(C.hid) = 1;
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!!!