Get Downline and Upline of hierarchical data and Performance review - CTE vs HierarchyId in SQL Server 2008

I have already posted for the HierarchyId and CTE (Common Table Expression), also given the comparison review of them for the level and hierarchical order data. I am not saying that HierarchyId is better than CTE or CTE is better then HierarchyId. But it all depends on. You need to practically use them and review the performance of hierarchies and CTE. I am going to show one more demo to find the members with downline and upline.

-- creating database and objects
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]())
     ,NodeStringPath as  (NodePath.ToString())
     ,NodeDesc  VARCHAR(100)
  )
GO 

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

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

SELECT 
* 
FROM HierarchyTab
GO

1. Get down line data using CTE and HierarchyId and compare the execution plan.

-- 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 ) 
WHERE [Level] + 1 < =3
)

SELECT 
* 
FROM cteLevels 
WHERE [Level] = 3
GO


-- With HierarchyId
DECLARE @DownlineNode HierarchyId = 
(SELECT NodePath FROM HierarchyTab WHERE NodeId = 1)

SELECT 
 NodePath.ToString() AS NodeStringPath,
 NodeId,
 NodeParent,
 NodeDepth,
 NodeLevel,
 NodeDesc
FROM HierarchyTab
WHERE NodePath.IsDescendantOf(@DownlineNode) = 1
AND NodeLevel = @DownlineNode.GetLevel() + 3
GO

When you run above script you have data and execution plans as follow,

2. Get up line data using CTE and HierarchyId and compare the execution plan.

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


UNION ALL

SELECT
    NodeId 
    ,NodeStringPath as StringPath
    ,NodeParent 
    ,NodeDepth
    ,[Level] + 1 AS [Level]
    ,NodeDesc 
FROM   HierarchyTab
       INNER JOIN cteLevels
         ON ( NodeId = Parent ) 
WHERE [Level] + 1 < =3
)

SELECT 
* 
FROM cteLevels 
WHERE [Level] = 3
GO


-- With HierarchyId
SELECT 
 NodePath.ToString() AS NodeStringPath,
 NodeId,
 NodePath.GetAncestor(3).ToString() as ParentOn4thPos,
 NodeParent,
 NodeDepth,
 NodeLevel,
 NodeDesc
FROM HierarchyTab
WHERE NodeId = 9
GO

The result set and execution plan when you run above script

Hope you like this and share your experience as comments.