Database Reference
In-Depth Information
select ID, ID.GetDescendant(null,null), 1
from dbo.HierarchyTest
where Level = @Level - 1
union all
select ID, ID.GetDescendant(Child,null), Num + 1
from CTE
where Num < @ItemPerLevel
)
insert into dbo.HierarchyTest(ID, Level)
select Child, @Level
from CTE
option (maxrecursion 0)
set @Level += 1
end;
select avg(datalength(ID)) from dbo.HierarchyTest;
Result:
-----------
5
Listing 14-20.
HierarchyId: Adding children nodes in-between existing nodes
truncate table dbo.HierarchyTest
go
declare
@MaxLevels int = 8
,@ItemPerLevel int = 8
,@Level int = 2
insert into dbo.HierarchyTest(ID, Level) values(hierarchyid::GetRoot(), 1);
while @Level <= @MaxLevels
begin
;with CTE(ID, Child, PrevChild, Num)
as
(
select ID, ID.GetDescendant(null,null), convert(hierarchyid,null), 1
from dbo.HierarchyTest
where Level = @Level - 1
union all
select ID,
case
when PrevChild < Child
then ID.GetDescendant(PrevChild, Child)
else ID.GetDescendant(Child, PrevChild)
