Sub tiers using hierarchyid

Before some months ago in my company CSSI, Ahmedabad. I have seen code in my current project which was implemented by one of my friend Hari Shankar Goud.
I am not able to post code from that project because it’s unauthorized.But i will give you explanation here.
We having one table name CONTRACT which having primary key CONTRACTID, CONTRACTNO and foreign key PARENTCONTRACTID.

---

CREATE TABLE CONTRACTS
(
    CONTRACTID INT IDENTITY(1,1) CONSTRAINT PK_CONTRACT_CONTRACTID PRIMARY KEY,
    CONTRACTNO VARCHAR(100),
    PARENTCONTRACTID INT CONSTRAINT FK_CONTRACT_PARENTCONTRACTID FOREIGN KEY REFERENCES CONTRACTS(CONTRACTID)
)
GO
INSERT INTO CONTRACTS VALUES('100000-000',NULL)
INSERT INTO CONTRACTS VALUES('100000-001',1)
INSERT INTO CONTRACTS VALUES('100000-002',1)
INSERT INTO CONTRACTS VALUES('100000-002-001',3)
INSERT INTO CONTRACTS VALUES('100000-002-002',3)
INSERT INTO CONTRACTS VALUES('100000-002-002-001',5)
INSERT INTO CONTRACTS VALUES('200000-000',NULL)
INSERT INTO CONTRACTS VALUES('200000-001',7)
INSERT INTO CONTRACTS VALUES('200000-001-001',8)
GO

---

Before :
I am trying to write code which looks like his first approach to show sub routines of contracts.

---

-- ASCII 65 = A, 90 = Z
WITH CONTRACTLISTS(ID,CONTRACTID,CONTRACTNO,PARENTCONTRACTNO,LEVEL)
AS
(
    SELECT
        CONVERT(VARCHAR,     
            REPLICATE('Z',ROW_NUMBER() OVER(ORDER BY CONTRACTID)/26)
            + CHAR(64 + ROW_NUMBER() OVER(ORDER BY CONTRACTID)%26 + CASE WHEN (ROW_NUMBER() OVER(ORDER BY CONTRACTID)%26 = 0) THEN -32 ELSE 0 END)
        ) ID,
        CONTRACTID,
        CONTRACTNO,
        CAST(NULL AS VARCHAR(50)),
        1
    FROM CONTRACT WHERE PARENTCONTRACTID IS NULL
    UNION ALL
    SELECT
        
        CONVERT(VARCHAR,     
            ID + '/' + REPLICATE('Z',ROW_NUMBER() OVER(ORDER BY C.CONTRACTID)/26)
            + CHAR(64 + ROW_NUMBER() OVER(ORDER BY C.CONTRACTID)%26 + CASE WHEN (ROW_NUMBER() OVER(ORDER BY C.CONTRACTID)%26 = 0) THEN -32 ELSE 0 END)
        ),
        C.CONTRACTID,
        C.CONTRACTNO,
        W.CONTRACTNO
        PARENTCONTRACTNO,
        LEVEL+1
    FROM CONTRACT C INNER JOIN CONTRACTLISTS W ON C.PARENTCONTRACTID = W.CONTRACTID
)
SELECT * FROM CONTRACTLISTS
ORDER BY ID

---

In above he has used Alphabets to shorting. After each complete set it’s repeating Z as prefix to keep sorting well.
Problems :
To display sub tiers contract list we need to execute above code every time, which spend more time to get your output.
After :
Using hierarchyid we can overcome this problem in same table.
Add new columns, Node as HierarcyID and NodeLevel as it’s Node level from root.Now we need to update HierarcyID for existing value as below.

---

IF NOT EXISTS(select 1 from sys.columns where name = 'Node' and object_id = object_id('CONTRACTS','U'))
    ALTER TABLE CONTRACTS ADD Node HIERARCHYID
IF NOT EXISTS(select 1 from sys.columns where name = 'NodeLevel' and object_id = object_id('CONTRACTS','U'))
    ALTER TABLE CONTRACTS ADD NodeLevel as Node.GetLevel()
UPDATE CONTRACTS SET Node = NULL
DECLARE @NodeRoot HierarchyId
DECLARE @MaxNode HierarchyId
SET @NodeRoot = HierarchyId::GetRoot()
SELECT @NodeRoot
DECLARE @mIndex INT
DECLARE @cIndex INT = 1
SELECT @mIndex = COUNT(*) FROM CONTRACTS WHERE PARENTCONTRACTID IS NULL AND Node IS NULL
--Update first tiers Contracts
WHILE @cIndex <= @mIndex
BEGIN
    SELECT @MaxNode = Node from CONTRACTS WHERE Node.GetAncestor(1) = @NodeRoot
   
    UPDATE TOP (1) CONTRACTS
    SET Node = @NodeRoot.GetDescendant(@MaxNode,NULL)
    WHERE Node IS NULL AND PARENTCONTRACTID IS NULL
   
    SET @cIndex += 1
END
--Update next tiers Contracts
;WITH CONTRACTLIST(CONTRACTID,NODE)
AS
(
    SELECT CONTRACTID,NODE FROM CONTRACTS WHERE NODE IS NOT NULL
    UNION ALL
    SELECT T.CONTRACTID, CAST(CL.NODE.ToString() + CAST(T.ROWNUMBER AS VARCHAR) + '/' AS HIERARCHYID)
    FROM
    (
        SELECT CONTRACTID,PARENTCONTRACTID, ROW_NUMBER() OVER(PARTITION BY PARENTCONTRACTID ORDER BY PARENTCONTRACTID) ROWNUMBER FROM CONTRACTS
    )T JOIN CONTRACTLIST CL ON T.PARENTCONTRACTID = CL.CONTRACTID
)
UPDATE C
    SET C.Node = CL.NODE
FROM CONTRACTLIST CL INNER JOIN CONTRACTS C ON C.CONTRACTID = CL.CONTRACTID
WHERE C.Node is null

---

Now see result using small code
SELECT Node.ToString(),* from CONTRACTS
ORDER BY Node

Maintain HierarchyId in table :
Insert :
IF EXISTS(SELECT 1 FROM SYS.objects WHERE TYPE = 'P' AND name = USP_INSERT_CONTRACT)
    DROP PROC USP_INSERT_CONTRACT
GO
CREATE PROCEDURE USP_INSERT_CONTRACT
(
    @CONTRACTNO VARCHAR(100),
    @PARENTCONTRACTNO VARCHAR(100)
)
AS
BEGIN
    DECLARE @PARENTCONTRACTID INT
    DECLARE @PARENTCONTRACTNODE HIERARCHYID
    DECLARE @MAXNODE HIERARCHYID
    DECLARE @NEWNODE HIERARCHYID
   
    SELECT
        @PARENTCONTRACTID = CONTRACTID,
        @PARENTCONTRACTNODE = NODE
    FROM CONTRACTS WHERE CONTRACTNO = @PARENTCONTRACTNO
   
    SET @PARENTCONTRACTNODE = ISNULL(@PARENTCONTRACTNODE,Hierarchyid::GetRoot())
   
    SELECT
        @MAXNODE = NODE
    FROM CONTRACTS WHERE NODE.GetAncestor(1) = @PARENTCONTRACTNODE
   
    SET @NEWNODE = @PARENTCONTRACTNODE.GetDescendant(@MAXNODE,NULL)
   
    INSERT INTO CONTRACTS(CONTRACTNO,PARENTCONTRACTID,Node) VALUES(@CONTRACTNO,@PARENTCONTRACTID,@NEWNODE)
END
GO 
EXEC USP_INSERT_CONTRACT '100000-002-001-001','100000-002-001'
EXEC USP_INSERT_CONTRACT '300000-000',NULL
Update
IF EXISTS(SELECT 1 FROM SYS.objects WHERE TYPE = 'P' AND name = 'USP_UPDATE_CONTRACT')
    DROP PROC USP_UPDATE_CONTRACT
GO
CREATE PROCEDURE USP_UPDATE_CONTRACT
(
    @CONTRACTNO VARCHAR(100),
    @PARENTCONTRACTNO VARCHAR(100)
)
AS
BEGIN
    DECLARE @OLDPARENTCONTRACTID INT
   
    DECLARE @PARENTCONTRACTID INT
    DECLARE @OLDPARENTCONTRACTNODE HIERARCHYID
    DECLARE @OLDCONTRACTNODE HIERARCHYID
    DECLARE @PARENTCONTRACTNODE HIERARCHYID
   
    DECLARE @MAXNODE HIERARCHYID
    DECLARE @NEWNODE HIERARCHYID
   
    SELECT
        @OLDPARENTCONTRACTNODE = Node.GetAncestor(1),
        @OLDCONTRACTNODE = Node
    FROM CONTRACTS WHERE CONTRACTNO = @CONTRACTNO
   
    SELECT
        @PARENTCONTRACTID = CONTRACTID,
        @PARENTCONTRACTNODE = NODE
    FROM CONTRACTS WHERE CONTRACTNO = @PARENTCONTRACTNO
   
    SET @PARENTCONTRACTNODE = ISNULL(@PARENTCONTRACTNODE,Hierarchyid::GetRoot())
   
    IF(@OLDPARENTCONTRACTNODE <> @PARENTCONTRACTNODE)
    BEGIN
        IF EXISTS(SELECT 1 FROM CONTRACTS WHERE Node.IsDescendantOf(@OLDCONTRACTNODE) = 1 AND CONTRACTNO = @PARENTCONTRACTNO)
        BEGIN
            RAISERROR('Parent Contract cannot be child of it's own',16,1)
            RETURN
        END
       
        SELECT @NEWNODE = @PARENTCONTRACTNODE.GetDescendant(MAX(Node),NULL)
        FROM CONTRACTS WHERE Node.GetAncestor(1) = @PARENTCONTRACTNODE
       
        UPDATE CONTRACTS SET
            Node = Node.GetReparentedValue(@OLDCONTRACTNODE,@NEWNODE)
        WHERE Node.IsDescendantOf(@OLDCONTRACTNODE) = 1   
    END   
   
    UPDATE CONTRACTS SET
    PARENTCONTRACTID = @PARENTCONTRACTID
    WHERE CONTRACTNO = @CONTRACTNO
   
END
GO
Benefits

• Performance improvement while displaying records.
• We can easily get any contract’s sub tiers records using IsDescendantOf.
• Get level of contract.
• No need of Parent Contract ID column in this case.
• If you apply index on Node than it will be more optimized.

References
http://technet.microsoft.com/en-us/library/bb677174.aspx