SET NOCOUNT ON;
GO
TRUNCATE TABLE dbo.HierarchyTest;
GO
INSERT INTO dbo.HierarchyTest
        (NodeName,
         ParentID,
         RangeStart,
         RangeEnd,
         HID
        )
 SELECT 
    'Person' + RIGHT('0000' + CAST(Number AS VARCHAR(4)), 4),
    NULL, NULL, NULL, NULL
  FROM dbo.Numbers; -- 10,001 rows of data
GO
DECLARE Cur CURSOR
FOR
    SELECT  ID
    FROM    dbo.HierarchyTest
    ORDER BY ID FOR UPDATE;

OPEN Cur;

DECLARE @ID INT,
    @PID INT,
    @HID VARCHAR(MAX);

FETCH NEXT FROM Cur INTO @ID;

BEGIN TRY;

    WHILE @@fetch_status = 0 
        BEGIN
            SET @HID = COALESCE(@HID + CAST(@ID AS VARCHAR(MAX)) + '/', '/1/');
	
            UPDATE  dbo.HierarchyTest
            SET     ParentID = @PID,
                    HID = CAST(@HID AS HIERARCHYID)
            WHERE CURRENT OF Cur;
	
            SET @PID = @ID;
	
            FETCH NEXT FROM Cur INTO @ID;
        END;

    CLOSE Cur;
    DEALLOCATE Cur;

END TRY
BEGIN CATCH;
    PRINT @ID;
END CATCH;

Jeff Moden (9/10/2012)

---

There are some people who insist on building the Nested Sets data in a single query. I don’t know why, since this does it accurately and in about 1 second on my machine. Surely that’s fast enough and efficient enough.

Yowch. I'm not sure what's going on but I have an I5 laptop w/6GB of RAM and I'm running SQL Server 2008 SP3 (version 10.0.5500.0). It' takes 00:02:34 for the first merge of the 10,000 row example to run and the execution plan shows an internal arrow with almost a 90 million rowcount. I thought it might be some form of parameter sniffinng and even restarted the instance but to no avail. Any idea what might be going on there?

WITH    Hierarchy(ID, PID, Lvl, Pth)
          AS (SELECT    ID,
                        NULL,
                        0,
                        '/' + CAST(ID AS VARCHAR(MAX)) + '/'
              FROM      dbo.HierarchyTest
              WHERE     ParentID IS NULL
              UNION ALL
              SELECT    HSub.ID,
                        HSub.ParentID,
                        Hierarchy.Lvl + 1,
                        Hierarchy.Pth + CAST(HSub.ID AS VARCHAR(MAX)) + '/'
              FROM      dbo.HierarchyTest AS HSub
                        INNER JOIN Hierarchy
                            ON HSub.ParentID = Hierarchy.ID)
    MERGE dbo.HierarchyTest AS H
        USING
            (SELECT  ID,
                    PID,
                    Lvl,
                    Pth FROM Hierarchy) AS Paths
        ON H.ID = Paths.ID
        WHEN MATCHED
            THEN UPDATE
               SET      H.HID = Paths.Pth ;

WITH    CTE
          AS (SELECT    ID,
                        ROW_NUMBER() OVER (ORDER BY HT1.HID) AS R
              FROM      dbo.HierarchyTest AS HT1
              WHERE     RangeStart IS NULL
                        AND RangeEnd IS NULL
                        AND NOT EXISTS ( SELECT *
                                         FROM   dbo.HierarchyTest AS HT2
                                         WHERE  HT2.ParentID = HT1.ID ))
    UPDATE  Tgt
    SET     RangeStart = R,
            RangeEnd = R
    FROM    dbo.HierarchyTest AS Tgt
            INNER JOIN CTE
                ON CTE.ID = Tgt.ID ;

WHILE @@ROWCOUNT > 0
    BEGIN
        WITH CTE
          AS (SELECT    
		         HT1.ID,
                 MIN(HT2.RangeStart) AS RS,
                 MAX(HT2.RangeEnd) AS RE
               FROM dbo.HierarchyTest AS HT1
                INNER JOIN dbo.HierarchyTest AS HT2
                  ON HT1.ID = HT2.ParentID
               WHERE HT1.RangeStart IS NULL
                 AND HT1.RangeEnd IS NULL
                 AND HT2.RangeStart IS NOT NULL
                 AND HT2.RangeEnd IS NOT NULL
               GROUP BY  HT1.ID)
      UPDATE  Tgt
       SET    RangeStart = CTE.RS,
              RangeEnd = CTE.RE
      FROM    dbo.HierarchyTest AS Tgt
       INNER JOIN CTE
        ON Tgt.ID = CTE.ID ;
    END ;