- From Which Edition is it Available
- SQL Server 2008
- What is HierarchyId?
- It is a datatype introduced in SQL Server to be used for identifying the parent / child relationships
- It is helpful in querying hierarchical data
- Examples of Hierarchial data include an Folder structure (root folder, sub folder, Items)
- Key Properties of HierarchyId
- It is variable length system datatype, the size depends on the number of children elements within the tree. i.e. for small trees that contain say 0-7 the size as mentioned in microsoft website is about 6*logAn bits, where A is the average number of children in the tree.
- Comparison is in depth order i.e. Level1
- Support for insertions and deletions i.e. GetDescendantMethod.
Looking at the organization chart above, the SQL table similar to this would look something like below. please take a look at examples given below at Microsoft Website
Key Methods available as part of hiearchyId are listed below.
Indexing strategies for Hierarchial data are based on two approaches 1) Breadth-first and Depth First.
Useful links:
