Analyzing Tempdb Spills and Usage Across Recursive Queries

Question

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27840 More actions · Answer 1

I may be mistaken as I have not tested this out

INSERT INTO @Hierarchy

SELECT

h.ID

, h.ParentID

, h.Name

, @Level + 1

FROM

HierarchyBig h

INNER JOIN @Hierarchy t

ON h.ParentID = t.ID;

For this and the temp table, wont this statement run indefinitely once it reaches the final level, if not for the max recursion setting?

----------------------------------------------------

Chandan Shukla SSC Veteran Points: 207 More actions · Answer 2

Hi MMartin1,

We need to seed the root node first.. This has come in screenshot but not in code.

INSERT INTO @Hierarchy (ID, ParentID, Name, Level)

SELECT ID, ParentID, Name, 1

FROM HierarchyBig

WHERE ParentID IS NULL;

Thanks for pointing that out . I’ll request an update to reflect the correct version. Appreciate the feedback!