HerichyID in MS SQL 2008

One of the most tuffs' queries is to finding the hierarchy. It may be employee or other things. It is always the headache of developer to make tree like structure.

In SQL 2000 we use temporary table to solve this. But in SQL 2005 gives us the better facility to work like this by CTE. An example of SQL 2005 CTE is given bellow. Hope you understand it.

Table Structure:

CREATE TABLE my_EMP

 (empid     int         NOT NULL primary key,

  empname   varchar(50) NULL,

  managerid int)

Values Inserted:

INSERT INTO my_EMP(empid, empname, managerid)

  VALUES(1, 'Sudip Das', NULL),(2, 'Joydeep Das', 1),(3, 'Tufan',2),

        (4, 'Sangram',2)

 

SELECT * FROM my_EMP

The Output:

empid   empname           managerid

1              Sudip Das            NULL

2              Joydeep Das      1

3              Tufan                   2

4              Sangram              2

 

The CTE and Output (Hierarchy level)

WITH empharicy(empid, empname, haricyOrder)

  AS

  (

     SELECT empid, empname, 1 as haricyOrder

     FROM   my_EMP

     WHERE  managerid IS NULL

     UNION ALL

     SELECT a.empid, b.empname, haricyOrder+1

     FROM   my_EMP a

            INNER JOIN empharicy b ON a.managerid=b.empid

  )

  SELECT * FROM empharicy

empid   empname           haricyOrder

1              Sudip Das            1

2              Sudip Das            2

3              Sudip Das            3

4              Sudip Das            3

 

Great news in SQL 2008, no pain for developer there is a new data type named HerichyID is your ultimate weapons.

Let's starts it.

To provide a real support of hierarchies, SQL Server 2008 introduces a new type of data: HierarchyID. It is a managed type (.NET), handled by the SQLCLR of SQL Server. It not only stores the parent elements but also stores the set of information to identify the entire hierarchy.   My main purpose of this article to convert our existing table named "my_EMP" to newly created table which contains data type HierarchyID. We are taking a new columns named "OrgNode" to understand the migration properly. So let's create the new table.     CREATE TABLE My_EEMP_Converted             (               OrgNode      hierarchyid,               empid        int,               empname      varchar(50),               managerid    int               CONSTRAINT PK_My_EEMP_Converted               PRIMARY KEY CLUSTERED (OrgNode)             );   Now create a temporary table in the name of "#children" to contain columns name "Num" that contains the number of children's in each node. Create index on it.   CREATE TABLE #Children    (     empid     int,     managerid int,     Num       int    );   GO  CREATE CLUSTERED INDEX tmpind ON #Children(managerid, empid);   Now populate the temporary table. Here ROW_NUMBER function is used to populate the "Num" columns. I know you have idea on it. INSERT #Children (empid, managerid, Num) SELECT empid, managerid,        ROW_NUMBER() OVER (PARTITION BY managerid ORDER BY managerid) FROM   my_EMP GO Now review the child table. SELECT * FROM #Children ORDER BY ManagerID, Num The desired result set is displayed. empid   managerid          Num 1              NULL                   1 2              1                            1 3              2                            1 4              2                            2   Now populate the table  "My_EEMP_Converted" WITH paths(path, empid) AS (       -- This section provides the value for the root of the hierarchy     SELECT hierarchyid::GetRoot() AS OrgNode, empid     FROM   #Children C     WHERE ManagerID IS NULL     UNION ALL     -- This section provides values for all nodes except the root     SELECT CAST(CONVERT(varchar,p.path)                 + CONVERT(varchar(30),C.Num)                 + '/' AS hierarchyid),            C.empid     FROM   #Children C            INNER JOIN paths p ON C.managerid = p.empid )     INSERT My_EEMP_Converted (OrgNode, O.empid, O.empname, O.managerid) SELECT P.path, O.empid, O.empname, O.managerid FROM   my_EMP AS O        INNER JOIN Paths AS P ON O.empid = P.empid GO   SELECT * FROM My_EEMP_Converted To more understanding of "OrgNode" convert it to string. SELECT CONVERT(VARCHAR,OrgNode)OrgNode , empid, empname, managerid FROM   My_EEMP_Converted   Now drop the temp table DROP TABLE #Children GO To help queries at the same level in the hierarchy, use the Get Level method to create a computed column that contains the level in the hierarchy. Create compound index on  level and the " Hierarchyid".   ALTER TABLE My_EEMP_Converted       ADD H_Level AS OrgNode.GetLevel()       CREATE UNIQUE INDEX My_EEMP_Converted_idx    ON My_EEMP_Converted(H_Level, OrgNode) GO   SELECT * FROM My_EEMP_Converted   Now create unique index of empid. Complete view SELECT OrgNode, CONVERT(varchar,OrgNode) logical_level,        empid, empname, managerid, H_Level FROM   My_EEMP_Converted   OrgNode             logical_level      empid   empname              managerid          H_Level 0x                          /                             1              Sudip Das        NULL                    0 0x58                     /1/                           2              Joydeep Das   1                            1 0x5AC0                 /1/1/                       3              Tufan              2                            2 0x5B40                 /1/2/                       4              Sangram          2                            2         Hope you like it.   Posted by: MR. JOYDEEP DAS