Database Reference
In-Depth Information
Spatial indexes can address some performance issues, although they work within the scope of the entire table.
All further predicate evaluation is done on later execution stages. This leads to suboptimal performance when spatial
operations are done on subsets of the data. You can use a bounding box approach and filter out the rows prior to
calling spatial methods to address this issue.
HierarchyId
types provide built-in support for hierarchical data. Although it has excellent query performance,
hierarchy maintenance is expensive. Every change in the hierarchy requires an update of the hierarchical path in
every child node. You must consider such overhead when data is volatile.
HierarchyId
types do not enforce correctness of the hierarchical structure. That must be done in the code. You
should also avoid inserting new nodes in between existing ones because it increases the size of the path stored.
Finally, support of system- and user-defined CLR types is not consistent across different development platforms.
You need to make sure that client applications can utilize them before making the decision to use them. Alternatively,
you can hide those types behind the data-access tier with T-SQL stored procedures when it is possible and feasible.
