SQLScientist.com: 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