How should I delete branch from adjacency list in SQL Server? Given a basic acyclic adjacency list like this:

What is the most effective way to handle deletions of branches? 

My current approach uses a recursive CTE to find all descendant nodes and their depths (n). Then I iterate (ugh) over those depths in reverse and issue n deletes.

CREATE TABLE [dbo].[TreeNode](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ParentNodeId] [int] NULL,
    [Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_TreeNode] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TreeNode]  WITH CHECK ADD  CONSTRAINT [FK_TreeNode_TreeNode] FOREIGN KEY([ParentNodeId])
REFERENCES [dbo].[TreeNode] ([Id])
GO

ALTER TABLE [dbo].[TreeNode]  WITH CHECK ADD  CONSTRAINT [CK_TreeNode_Id_ParentNodeId] CHECK  (([Id]&lt;&gt;[ParentNodeId]))
GO


ON DELETE CASCADE won't work here because it isn't allowed in self-referencing FKs
No need for a procedure, just combine the CTE with a DELETE

with doomed as (
  select id, ParentNodeId
  from treenode
  where id = ... --&lt;&lt; start node of the branch to be deleted
  union all
  select c.id, c.ParentNodeId
  from treenode c
    join doomed p on c.parentnodeid = p.id
)
delete from treenode 
where id in (select id from doomed);