CLR Types - Pro SQL Server Internals - page 293

end, Child, 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:
-----------
11

As you see, adding children in between existing nodes in the hierarchy more than doubled the size of the
path stored.

■ The HierarchyId data type has an additional two bytes of overhead stored in variable-length data offset array
in every row.

Note

The key point that you need to remember is that the HierarchyId data type persists a hierarchical path, and
it provides a set of methods that help when working with hierarchical data. It does not enforce the correctness of the
hierarchy stored in a table, nor the uniqueness of the values. It is your responsibility to enforce it in the code.
Maintenance of hierarchical data is expensive. Changing the path for the node with the children requires
an update of the path in every child node. This leads to the update of multiple rows in the table. Moreover,
the HierarchyId column is usually indexed, which introduces physical data movement and additional index
fragmentation, especially when the HierarchyId column is part of a clustered index. You need to keep this in mind
when designing an index maintenance strategy for tables with HierarchyId columns when the data is volatile.

Summary
User-defined CLR data types allow us to expand the standard SQL Server type library. Unfortunately, this flexibility
has a price. CLR data types are stored in the database in binary format and accessing the object properties and
methods leads to deserialization and CLR method calls. This can introduce serious performance issues when those
calls are done for a large number of rows.
You can reduce the number of CLR calls by adding persistent calculated columns that store the results of
frequently accessed properties and methods. At the same time, this increases the size of the rows and introduces the
overhead when data is modified.
Another important aspect is maintainability. SQL Server does not support the ALTER TYPE operation. It is
impossible to change the interface of existing methods or utilize new methods of the type until it is dropped
and recreated.
Geometry and geography types help us work with spatial data. They provide a rich set of methods used to
manipulate the data, although those methods are usually expensive and can lead to poor performance when called
for a large number of a rows.