Day 6 of my virtual advent calendar, about stuff I like in SQL Server 2008..
Using a relational database to store structures like the hierarchy of a company or the product catalogue usually involves one of two techniques:
The downside of this self join approach is that is slow to navigate and awkward to report on. So In SQL Server 2008 there is a new HierarchyID data type. This stores the structure of the hierarchy in a system defined user defined type (UDT) like there is in SQL Server for spatial data. and like spatial data there are three things you get:
Of course this is one new feature which will require development time, but it is a lot faster and intuitive then self joins while having the same advantage of not requiring schema changes to reflect changes in the number of levels you need.
If you want to try it then I have put a simple example in this separate article on my blog.
PingBack from http://economy.cheap-lcd.info/economy/mid-to-large-companies-looking-for-etldata-migration-and-business/