Next Available Node with HierarchyId Data Type - SQL Server 2008 by serverku

I have posted for the some of the functions with examples and demonstrate them in earlier posts. I have written articles of HierarchyId data type overview, some basic functions, even demonstrate for the up-line & down-line of hierarchy nodes which you can read from below links.

Here I will present how can we get the next available node to be planed with HierarchyID data type function. Let's first create hierarchy data structure with the script.

-- Create a database and table
CREATE DATABASE HierarchyDB

GO

USE HierarchyDB

GO

IF ( Object_id('HierarchyTab') > 0 )
DROP TABLE HierarchyTab

GO

CREATE TABLE HierarchyTab
(
NodeId     INT IDENTITY(1, 1)
,NodeDepth VARCHAR(100) NOT NULL
,NodePath  HIERARCHYID NOT NULL
,NodeDesc  VARCHAR(100)
)

GO 

-- Creating constraint on hierarchy data type.
ALTER TABLE HierarchyTab ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)

GO 
-- Inserting data in above creatd table.
INSERT INTO HierarchyTab(NodeDepth,NodePath,NodeDesc)
VALUES 
('1',HIERARCHYID::Parse('/'),'Node-1'),
('1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
('1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
('1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
('1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
('1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
('1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
('1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
('1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9'),
('1.2.2.1.2',HIERARCHYID::Parse('/2/2/1/2/'),'Node-10'),
('1.3',HIERARCHYID::Parse('/3/'),'Node-11'),
('1.3.1',HIERARCHYID::Parse('/3/1/'),'Node-12'),
('1.3.2',HIERARCHYID::Parse('/3/2/'),'Node-13'),
('1.4',HIERARCHYID::Parse('/4/'),'Node-14')

GO

Hierarchy data structure as imaged as below.

(Click on image to enlarge)

How to we find it?

GetDescendant() : This function will give next available node where we can place new node.

1. GetDescendant(NULL,NULL) : will return default next left node.
2. GetDescendant(LeftNode,NULL) : will return right node next to left node.
3. GetDescendant(NULL,RighNode) : will return left node previous to left node.

Run the following script and see the output.

-- GetDescendant()
SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetDescendant(NULL,NULL).ToString() AS NextDefaultNode,
-- Above line will get default node.
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab

GO

Looking for one hierarchy node id 13,

SELECT 
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodePath.GetDescendant(NULL,NULL).ToString() AS NextDefaultNode,
NodePath.GetDescendant(HIERARCHYID::Parse('/3/2/1/'),NULL).ToString() AS NextRightNode,
NodePath.GetDescendant(NULL,HIERARCHYID::Parse('/3/2/2/')).ToString() AS NextLeftNode,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
WHERE NodeId = 13

GO

(Click on image to enlarge)

This is what I want to share here and hope you like it.