Today we continue our series on hierarchy structures in a database. This last implementation I’m sharing is only known to me in SQL Server. Microsoft has implemented a Hierarchy data type, having built in methods for handling relationships between nodes.
You can insert a record into the hierarchy as the last node of a parent node, or following a specific node. You need to determine the insertion point, locate the specific node, and then insert the new record passing a pointer to the previous sibling node. If this is the first child of a node, a pointer can be found for where this new, first, node will be inserted.
While it does implement methods for performing inserts, updates and deletes, there are holes in the data type which must be addressed by the calling application. For example, you can delete the parent node without removing any descendants. This results in having orphaned records, which, if not a desirable implementation, you must handle in your own application.
The biggest value in using the SQL Server hierarchy data type is efficiency in storage. The Hierarchy data type is not intended for any and all hierarchy needs. For more information, see the Limitations of HierarchyID in https://msdn.microsoft.com/en-us/library/bb677173.aspx for more information.
Are there other hierarchy implementations you like do use? Perhaps they are specific to another SQL engine, or something you find helpful? Share your thoughts in comments here, or drop an email to btaylor@sswug.org.
Cheers,
Ben