Sometimes I get the question of when Anchor Modeling is not suitable. “Actually, most of the times it is”, is my common answer. However, there are times when the requirements are such that you need a bit of trickery on top of a model. One such case recently emerged at a client. The dilemma was how to ask polymorphic graph queries in SQL Server, when you have a network represented as a parent-child-relationship in your Anchor model? First, a polymorphic graph query is one in which you want to find nodes of certain properties connected through any number of edges in your network. For example, “find all computers that at any point have a wireless connection between them”. You may think that the new graph table types in SQL Server 2017 would solve this, but alas, they do not support these types of queries (yet).
Fortunately, in and since SQL Server 2008, an often overlooked new data type was introduced: HIERARCHYID. At first glance it looks disappointing, but it turns out that by using string searches and manipulation, polymorphic queries can be asked. Below is an example that shows how this is done, and should of course be applicable for any type of network, and not just the ones containing computers, switches, routers, cables and wireless connections. As a small bonus, a hint is also given of how to solve the traveling salesman problem.
If you want a general introduction to hierarchical data in databases I can recommend the guide “Hierarchical Data in SQL” by Ben Brumm.