Sort using Hierarchyid of SQL Server 2008

Introduction

SQL Server 2008, introduced the hierarchyid. This is basically use for storing the hierarchical data.Though it's been a long time that it got introduced, we found a situation recently where we felt the use of this feature and in this article, we will share the experience of that.

Requirement

Suppose we have the below table

--Input Script
--Declare a sample table variable
DECLARE @tblAssemblyVersion TABLE(Version VARCHAR(20))

--Insert some records to it
INSERT INTO @tblAssemblyVersion VALUES
('6.0.0.0'),('3.0.0.0'),('4.5.1.0'),('4.5.8.0'),('4.5.11.0'),('1.1.2.14234'),('1.11.21.14234'),('1.8.2.14234'),('6.0.0.1')

--Display that
SELECT * FROM @tblAssemblyVersion

/*	OUTPUT
	
	Version
	--------
	6.0.0.0
	3.0.0.0
	4.5.1.0
	4.5.8.0
	4.5.11.0
	1.1.2.14234
	1.11.21.14234
	1.8.2.14234
	6.0.0.1
*/

And we have been asked to sort the assemblies.So how can we do that?

The more general problem statment

A normal "OrderBy" clause won't work here.If we apply that, we will receive the below output

SELECT *
FROM @tblAssemblyVersion
ORDER BY 1

/*	OUTPUT
	
	Version
	--------
	1.1.2.14234
	1.11.21.14234
	1.8.2.14234
	3.0.0.0
	4.5.1.0
	4.5.11.0
	4.5.8.0
	6.0.0.0
	6.0.0.1
*/

As can be figure out that "1.11.21.14234" came before "1.8.2.14234" which is not desirous.The same problem between "4.5.11.0" and "4.5.8.0" The question why such a problem arise? Well , it is a typical problem of "don't store delimited values in columns". A good amount of discussion regarding this has been done here

Using the code

Among the other options lets go ahead with the Hierarchyid. If you properly note, we have not created the Column type as hierarchyid. However.However,we will perform the sorting based on that.The code segment for the same is as under

SELECT * FROM @tblAssemblyVersion
ORDER BY CAST('/'+REPLACE(Version,'.','/')+'/' AS hierarchyID)

/*	OUTPUT
	
	Version
	--------
	1.1.2.14234
	1.8.2.14234
	1.11.21.14234
	3.0.0.0
	4.5.1.0
	4.5.8.0
	4.5.11.0
	6.0.0.0
	6.0.0.1
*/

So we receive the require output that we have asked for. So what's the magic behind?. Well, the hierarchyid data type values represents a position in a tree hierarchy and it performs Depth-first search of the tree traversal.That indicates that for any two hierarchyid values e.g. x and y, if x and y are in a relationship of x<y, then in the case of Depth-first search, x will always appear before y. And hence, we got the correct answer.

References

a)Using HierarchyID system data type in SQL Server 2008

b)Hierarchical Data

Conclusion

Hope you will find this useful at some point of time.Try this out in a similar kind of situation where you find the need of a Hierarchical Data traversal using Depth-first search. Find attached code for the experiment herewith in zipped format for your reference.Thanks for reading the article.