To test maximum depth for HierarchyID, I just ran this test (same table definition as in the article):

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;

It'll fail when it reaches the greatest depth it can manage. Got successfully to 427 in this test.

If the node values were compressed, instead of ascending base-10 numbers, you could obviously squeeze more depth out of it, but I don't have time to try that right now. Convert the base-10 to base-36, for example, and you'd get a lot more depth, since it's dependent on the length of the string that's being converted. I haven't tried that yet, so it could just crash and burn. Probably worth a test, though.