Thursday 15 March 2012

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

No comments:

Post a Comment