Breath First and Depth First Strategy with Hierarchical Data and Performance review - HierarchyId Data Type vs CTE in SQL Server 2008 by serverku

It is very important to check the performance if we use the new features of alternative methods of SQL Server. I have written earlier posts of the HierarchyId and CTE (Common Table expression). Now in this post you can view the usage of HierarchyId and CTE, comparisons between them. Go ahead with the object creation.

1. Introduction to HierarchyId data type - Amazing feature of SQL Server 2008
2. HierarchyId DataType in SQL Server 2008 - Get Level and String Path

-- Creating databse and table
CREATE DATABASE HierarchyDB
GO

USE HierarchyDB
GO

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

CREATE TABLE HierarchyTab
(
  NodeId     INT NOT NULL
 ,NodeParent int
 ,NodeDepth VARCHAR(100) NOT NULL
 ,NodePath  HIERARCHYID NULL
 ,NodeLevel as (NodePath.[GetLevel]()) 
 ,NodeDesc  VARCHAR(100)
)
GO 

ALTER TABLE HierarchyTab 
ADD CONSTRAINT U_NodePath UNIQUE CLUSTERED (NodePath)
GO 

Inserting demo records in table created above.

INSERT INTO HierarchyTab(NodeId,NodeParent,NodeDepth,NodePath,NodeDesc)
VALUES 
(1,NULL,'1',HIERARCHYID::Parse('/'),'Node-1'),
(2,1,'1.1',HIERARCHYID::Parse('/1/'),'Node-2'),
(3,2,'1.1.1',HIERARCHYID::Parse('/1/1/'),'Node-3'),
(4,2,'1.1.2',HIERARCHYID::Parse('/1/2/'),'Node-4'),
(5,1,'1.2',HIERARCHYID::Parse('/2/'),'Node-5'),
(6,5,'1.2.1',HIERARCHYID::Parse('/2/1/'),'Node-6'),
(7,5,'1.2.2',HIERARCHYID::Parse('/2/2/'),'Node-7'),
(8,7,'1.2.2.1',HIERARCHYID::Parse('/2/2/1/'),'Node-8'),
(9,8,'1.2.2.1.1',HIERARCHYID::Parse('/2/2/1/1/'),'Node-9')
GO

Now we will check the usage  and execution plan of the HierarchyId and CTE.

1. First, we will run the scripts for the to get data level by level order. Let’s start with CTE and the query without using a hierarchy node,

-- Using CTE (Not Using NodePath and NodeLevel)
;WITH cteLevels
AS
(
SELECT
  NodeId as Node
 ,NodeParent as Perent 
 ,NodeDepth as Depth
 ,0        AS [Level]
 ,NodeDesc as [Desc]
FROM   HierarchyTab 
WHERE NodeId = 1


UNION ALL

SELECT
  NodeId 
 ,NodeParent 
 ,NodeDepth
 ,[Level] + 1 AS [Level]
 ,NodeDesc 
FROM   HierarchyTab
INNER JOIN cteLevels
ON ( NodeParent = Node ) 

)

select 
* 
from cteLevels 
Order by [Level]
GO

Now running the script to get hierarchy data level by level order using HierarchyId,

-- With HierarchyId
SELECT 
  NodePath.ToString() AS NodeStringPath,
  NodeId,
  NodeParent,
  NodeDepth,
  NodeLevel,
  NodeDesc
FROM HierarchyTab
Order by NodeLevel

GO

Before going ahead with next script, we are checking the execution plan of both above script,

2. Getting records of hierarchy in hierarchical order with CTE and HierarchyId.

-- Using CTE (Not Using NodePath and NodeLevel)
;WITH cteLevels
AS
(
SELECT
  NodeId as Node
 ,NodeParent as Perent 
 ,NodeDepth as Depth
 ,0        AS [Level]
 ,NodeDesc as [Desc]
 ,CAST(NodeId AS VARCHAR(MAX))  AS [Order]
FROM   HierarchyTab 
WHERE NodeId = 1


UNION ALL

SELECT
  NodeId 
 ,NodeParent 
 ,NodeDepth
 ,[Level] + 1 AS [Level]
 ,NodeDesc 
 ,[Order] + '.' + CAST(NodeId AS VARCHAR(MAX)) AS [Order]
FROM   HierarchyTab
INNER JOIN cteLevels
ON ( NodeParent = Node ) 

)

SELECT 
* 
FROM cteLevels 
ORDER BY [Order]

GO

View the result of hierarchical order data with running below query with HierarhyId,

-- With HierarchyId
SELECT 
 NodePath.ToString() AS NodeStringPath,
 NodeId,
 NodeParent,
 NodeDepth,
 NodeLevel,
 NodeDesc
FROM HierarchyTab
Order by NodePath

GO

Finally, we go through the performance and the execution plan review of both above scripts,

This is just the details of execution and performance review.