Hierarchyid Subtree: Move With GetReparentedValue

Moving a hierarchyid subtree means changing the root path and every descendant path together. GetReparentedValue preserves their relative positions under a new address. Keep a separate stable node identifier for references.

Separate node identity from hierarchyid location

NodeId identifies a row across moves. NodePath represents its current location. The sample moves a branch and its child while retaining both identifiers.

A unique path index prevents duplicate addresses. The hierarchyid type alone does not enforce parent existence or a complete tree. Those rules need an appropriate schema and maintained write procedure.

Choose a new child address before moving

Read the source and destination inside the protected operation. Reject missing endpoints and a destination inside the source subtree. This example also rejects moves of its root.

GetDescendant chooses an address after the destination’s last immediate child. It calculates an address; it does not reserve it against another writer. Destination allocation and the subtree update must share a suitable concurrency strategy.

Run the hierarchyid subtree example

This works on SQL Server 2012 or later and uses one temporary table. First, create the table and look at the starting paths.

DROP TABLE IF EXISTS #Nodes;

CREATE TABLE #Nodes
(
    NodeId int NOT NULL PRIMARY KEY NONCLUSTERED,
    NodePath hierarchyid NOT NULL UNIQUE CLUSTERED,
    Label nvarchar(30) NOT NULL
);

INSERT #Nodes VALUES
    (1, hierarchyid::Parse('/'),     N'Root'),
    (2, hierarchyid::Parse('/1/'),   N'Branch'),
    (3, hierarchyid::Parse('/1/1/'), N'Child'),
    (4, hierarchyid::Parse('/2/'),   N'Destination');

SELECT NodeId, Label, NodePath.ToString() AS OldPath
FROM #Nodes
ORDER BY NodeId;

Next, a temporary procedure does the move. A transaction and an exclusive table lock protect this small example.

CREATE PROCEDURE #MoveSubtree @SourceId int, @DestinationId int
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Old hierarchyid, @Parent hierarchyid, @Last hierarchyid,
            @New hierarchyid, @RowsMoved int = 0;

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT @Old = NodePath FROM #Nodes WITH (TABLOCKX, HOLDLOCK)
        WHERE NodeId = @SourceId;
        SELECT @Parent = NodePath FROM #Nodes
        WHERE NodeId = @DestinationId;

        IF @Old IS NULL OR @Parent IS NULL
            THROW 51502, 'A move endpoint does not exist.', 1;
        IF @Old.GetLevel() = 0
            THROW 51504, 'This example does not move the root.', 1;
        IF @Parent.IsDescendantOf(@Old) = 1
            THROW 51503, 'The destination lies inside the source subtree.', 1;

        IF @Old.GetAncestor(1) <> @Parent
        BEGIN
            SELECT @Last = MAX(NodePath) FROM #Nodes
            WHERE NodePath.GetAncestor(1) = @Parent;

            SET @New = @Parent.GetDescendant(@Last, NULL);

            UPDATE #Nodes
            SET NodePath = NodePath.GetReparentedValue(@Old, @New)
            WHERE NodePath.IsDescendantOf(@Old) = 1;

            SET @RowsMoved = @@ROWCOUNT;
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;
    END CATCH;

    SELECT @RowsMoved AS RowsMoved;
END;

Now move the branch, then send the same request again.

-- Move the branch under the destination, then repeat the same request.
EXEC #MoveSubtree @SourceId = 2, @DestinationId = 4;
EXEC #MoveSubtree @SourceId = 2, @DestinationId = 4;

SELECT NodeId, Label, NodePath.ToString() AS NewPath,
       NodePath.GetLevel() AS TreeLevel
FROM #Nodes
ORDER BY NodeId;

Three requests should be rejected: a cyclic destination, a missing endpoint and a root move.

-- Three requests that are rejected.
BEGIN TRY EXEC #MoveSubtree @SourceId = 2, @DestinationId = 3; END TRY   -- inside its own subtree
BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

BEGIN TRY EXEC #MoveSubtree @SourceId = 2, @DestinationId = 999; END TRY -- missing endpoint
BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

BEGIN TRY EXEC #MoveSubtree @SourceId = 1, @DestinationId = 4; END TRY   -- root move
BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH;

Last, check for orphans and clean up.

SELECT COUNT(*) AS OrphanCount
FROM #Nodes AS n
WHERE n.NodePath.GetLevel() > 0
  AND NOT EXISTS
      (SELECT 1 FROM #Nodes AS p
       WHERE p.NodePath = n.NodePath.GetAncestor(1));

DROP PROCEDURE #MoveSubtree;
DROP TABLE #Nodes;

Check the moved rows and the rejected requests

The expected branch path changes from /1/ to /2/1/. Its child changes from /1/1/ to /2/1/1/. Their levels become two and three, while NodeId stays unchanged.

The second request already names the current parent. The procedure leaves every path unchanged and reports zero moved rows. This avoids allocating another address for an unnecessary repeat.

The remaining requests try a cyclic destination, a missing endpoint and a root move. The error numbers are 51503, 51502 and 51504. Each rejection rolls back its transaction, and the final orphan count should be zero.

Keep the entire subtree and its consumers consistent

IsDescendantOf includes the source root itself. The update therefore moves the branch and its descendants using one transformation. Updating only the parent row would leave the descendant addresses behind.

A production move can affect many indexed paths and generate substantial logging and blocking. Rehearse its concurrency and cost with representative trees. This temporary table does not show how competing production sessions behave.

Check consumers that cache paths or store a separate parent identifier. A path move does not automatically update those representations. Keep their changes within the same maintained operation when consistency requires it.

Move a branch once on a throwaway tree and the rules start to feel natural.

A moved subtree is not one updated row, it is every descendant path at once.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.