TechSapphire Tuesday, 2024-03-19, 7:44 AM
Site menu
Login form
News
Play Games
  • Deep Freeze
  • Ice Slide
  • Gyroball
  • Fat Fish
  • Bush Royal Rampage
  • Learn Hierarchies in SQL server using HierarchyID

    What is Hierarchy ID ?

    • —Hierarchy ID is nothing other then column which represent relational value in numeric format on which DFS (Depth First Search) & BFS (Breadth First Search) can be used
    • —The concept is very simple, when we have to make relational structures which shows parent child level tree hierarchy, we can use hierarchyid datatype.

    What Hierarchy ID do ?

    • —With hierarchy ID recursive queries can be converted into range query.
    • —DFS (Depth First Search) and BFS (Breadth First Search) can be implemented using additional index which improves performance.
    • —Uses .NET assembly library defined methods to provide easy maintenance.
    • —Hierarchy ID is scalar column and is sequence of no. of var binary values.
    • —Note: Range query are far better in performance compared to recursive queries.

    Issue ?​

    • —Integrity is not maintained automatically. Integrity is need to be maintained.

    Lecture covers:

    • —Introduction
    • —Use of Function
    • —GetRoot()
    • —GetAncestor()
    • —IsDescendantOf()
    • —GetDescendant()
    • —GetReparentedValue()
    • —Converting existing relational hierarchy structure to hierarchyid implementation. 

    Functions

    —GetLevel()                     :  Returns level of current node.
    —GetAncestor(nth)           :  Returns nth ancestor of current node.
    —IsDescendantOf(Pnode) : Returns all nodes where parent is Pnode
    —GetDescendant(child1,child2) : Returns hierarchyid between child1 and child2. In this child1 and child2 can be NULL depending upon requirement.
    —GetReparentedValue(oldRoot, newRoot): Return hierarchyid of node as per newRoot.
     
    Script used:
    DECLARE @hid HIERARCHYID
    
    SET @hid = '/1/2/3/'
    
    --SELECT @hid as [HierarchyID]
    --ToString and CAST on hierarchy id
    --SELECT @hid [Var Binary],@hid.ToString() [As String],
    --CAST(@hid AS VARCHAR) [As String by CAST]
    --GetAncestor(nth) on hierarchy id
    SELECT @hid.ToString(), @hid.GetAncestor(1).ToString() [Parent], @hid.GetAncestor(2).ToString() [Grand Parent], @hid.GetAncestor(3).ToString() [Root], @hid.GetAncestor(4).ToString() [No Parent]
    WITH CTE(chain, NAME) AS (
     SELECT CAST('/1/' AS HIERARCHYID), 'Ram'
     
     UNION ALL
     
     SELECT '/2/', 'Ron'
     
     UNION ALL
     
     SELECT '/1/1/', 'Sahil'
     
     UNION ALL
     
     SELECT '/1/2/', 'Mohit'
     
     UNION ALL
     
     SELECT '/2/1/', 'Maze'
     
     UNION ALL
     
     SELECT '/', 'Boss'
     )
    
    SELECT *
    INTO #TEMP
    FROM CTE
    
    --INSERT INTO #TEMP VALUES('/1/2/1/','Don')
    SELECT *, chain.ToString()
    FROM #TEMP
    
    --GetRoot, GetLevel() on hierarchyid
    SELECT NAME, chain, chain.ToString() [String Value], HIERARCHYID::GetRoot().ToString() [Root], chain.GetLevel() [Level]
    FROM #TEMP
    ORDER BY chain.GetLevel()
    
    --IsDescendantOf(node) on hierarchyid
    DECLARE @node HIERARCHYID
    
    SET @node = '/1/';
    
    SELECT NAME, chain, chain.ToString() [String Value], HIERARCHYID::GetRoot().ToString() [Root], chain.GetLevel() [Level]
    FROM #TEMP
    WHERE chain.IsDescendantOf(@node) = 1
     AND chain != @node
     AND chain.GetLevel() = @node.GetLevel() + 1
    ORDER BY chain.GetLevel()
    
    --GetDescendant() on hierarchyid
    DECLARE @leftNode HIERARCHYID
    DECLARE @rightNode HIERARCHYID
    
    SET @leftNode = '/2/1/';
    SET @rightNode = '/2/1/';
    
    WITH CTE (node)
    AS (
     SELECT @leftNode.GetAncestor(1).GetDescendant(MAX(@rightnode), NULL) [GeneratedID]
     )
    SELECT node, 'More'
    FROM CTE
    
    --GetReparentedValue(oldNode,newNode) on hierarchyid
    DECLARE @oldNode HIERARCHYID, @newNode HIERARCHYID
    
    SET @oldNode = '/1/';
    SET @newNode = '/2/3/';
    
    SELECT chain.ToString() [OldNode], NAME, chain.GetReparentedValue(@oldNode, @newNode).ToString() [NewNode]
    FROM #TEMP
    WHERE chain.IsDescendantOf(@oldNode) = 1
    
    DROP TABLE #TEMP
     

    Click here to download script.

     

    Categories
    Programming [27]
    Tips for programming
    Security [2]
    Security Tips
    Google [1]
    Use google faster then ever you use
    Project [14]
    HTML [2]
    Electronics [0]
    Data Structure [0]
    Database [16]
    SQL SERVER
    SSRS [1]
    Sql Server Reporting Services
    Copyright MyCorp © 2024