In
previous post I have published of the overview of HierarchyId data type. In this post I am writing about details and the methods with hierarchy with HierarchyId data type.
Before go ahead with this post, please read the previous post here.
The example which I am going to demonstrate HierarchyId and the methods for the same will drive you in details and towards of better understanding.
Let’s create database and table objects.
-- Create 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
Now inserting some sample records of hierarchical data.
-- 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
Here is the logical image for the above data are as following,
Now we will look for the some of the methods of HierarchyId data type. We will go through GetRoot, ToString, Parse and GetLevel methods here and demonstrate in details for each.
Let's drive with me here. Before please my earlier post of overview of such methods here.
1. GetRoot()
-- GetRoot()
SELECT
HIERARCHYID::GetRoot() AS RootNode
,HIERARCHYID::GetRoot().ToString() AS RootNodePath
GO
RootNode RootnodePath
0x /
2. ToString()
-- ToString()
SELECT
NodePath.ToString() AS NodeStringPath
,NodeId
,NodeDepth
,NodePath
,NodeDesc
FROM HierarchyTab
GO
3. Parse()
-- Parse()
SELECT
HIERARCHYID::Parse(NodePath.ToString()) AS NodeVarBinaryPath,
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
GO
4. GetLevel()
-- GetLevel()
SELECT
NodePath.GetLevel() AS NodeLevel,
NodePath.ToString() AS NodeStringPath,
NodeId,
NodeDepth,
NodePath,
NodeDesc
FROM HierarchyTab
GO
Now you have a very good idea and understanding for HierarchyId data type after reading this post. You can also read the
Hiercrchyd Datatype Overview.
I will write next future post for other methods of HierarchyId data type.