Here I Explained the New Data Type in Sqlserver 2008.
What is HierarchIyd Data Type?
HierarchyId data type is a variable length System Data Type.
What is the use of HierarchyId Data Type?
Represent position in a hierarchy manner.
HierarchyId does not automatically represent a tree, it is depend on how to application generate and assign HierarchyId values.
HierarchyId have following properties:
1) Extremely Compact
2) Comparison is in depth-first-order.
3) Support for arbitrary insertions and deletions.
Limitation Of HierarchyId :
1) Does not automatically represent a Tree
2) It is up to Application to manage concurrency in generating and assigning HierarchyId Values.
3) There is no any dependency on HierarchyId relationship.
See more about HierarchyId example refer :
Reference Site :