Database Reference
In-Depth Information
Figure 14-15.
Inserting data
Let's test how
HierarchyId
generation affects the path size and create the table shown in Listing 14-18.
Listing 14-18.
HierarchyId: Test table
create table dbo.HierarchyTest
(
ID hierarchyid not null,
Level tinyint not null
)
The code shown in Listings 14-19 and 14-20 creates an eight-level hierarchy with eight children per node.
We will compare the average data size of
HierarchyId
data when children nodes are inserted as the rightmost nodes
(Listing 14-19) and when they are inserted in between existing nodes (Listing 14-20).
Listing 14-19.
HierarchyId: Adding children nodes as rightmost 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, Num)
as
(
