A Game of Hierarchies: From Joins to Recursive CTEs
Hierarchies are the bread and butter of most business applications and you find them almost everywhere:
* Product Categories
* Sales Territories
* Calendar and Time
Even when there is a big need from a business perspective, the solutions in relational databases are still sort of awkward. Since Version 2008, SQL Server makes life a bit easier with the data type hierarchyid. If you want to successfully query self-referenced hierarchies, you will need recursive common table expressions. To make use of aggregations, you have to master GROUP BY clauses, as well as GROUPING SETS, CUBE, ROLLUP, WITH CUBE, and WITH ROLLUP.
If these terms caught your attention, then join this session for a journey through best practices to model your hierarchies and handy scripts to transform your hierarchies into useful information. We will have fun playing around with a sample database based on G. R. R. Martin’s famous "Game of Thrones".