As you may know, While hierarchical tree structures are commonly used in many applications, SQL Server has not made it easy to represent and store them in relational tables. After SQL Server2000, XML structures are excellent for managing hierarchical data relationships, of course in SQL Server 2005, developers can use recursive queries against data stored hierarchically, but it needs to use CTE(common table expressions).

In Oracle, there is one way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is(same concept as we do laster in Ms Sql server 2008):

This table is now filled with some values:

Showing the tree with start with .. connect by:

With the release of SQL Server 2008, now you can store hierarchical data better than before, and also provides new functions to query data without needing to use common table expressions. In SQL Server 2008, the HIERARCHYID data type has been added to help resolve this problem. It is designed to store values that represent the position of nodes of a hierarchal tree structure.

For example, the HIERARCHYID data type makes it easier to express these types of relationships without requiring multiple parent/child tables and complex joins.

l           Organizational structures

l           A set of tasks that make up a larger projects (like a GANTT chart)

l           File systems (folders and their sub-folders)

l           A classification of language terms

l           A bill of materials to assemble or build a product

l           A graphical representation of links between web pages

Unlike standard data types, the HIERARCHYID data type is a CLR user-defined type, and it exposes many methods that allow you to manipulate the date stored within it. For example, there are methods to get the current hierarchy level, get the previous level, get the next level, and many more. In fact, the HIERARCHYID data type is only used to store hierarchical data; it does not automatically represent a hierarchical structure. It is the responsibility of the application to create and assign HIERARCHYID values in a way that represents the desired relationship. Think of a HIERARCHYID data type as a place to store positional nodes of a tree structure, not as a way to create the tree structure.

Here’s a simple example of a product hierarchy structure (family, brand, category, subcategory, etc.) stored in a single table called ProductTree. First, the syntax to create the ProductTree table is as follows:

CREATE TABLE ProductTree
   (ProductTreePK int IDENTITY, 
    Description varchar(1000), 
    ProductLevelPK int, HierID hierarchyid)

Next, here’s a foreign table of descriptions for each ProductLevel:

CREATE TABLE  ProductLevels 
             (ProductLevelPK int IDENTITY, 
             Description varchar(50))
-- PK of 1 (All)
INSERT INTO ProductLevels VALUES ('All Products')
-- PK of 2 (Family)
INSERT INTO ProductLevels VALUES ('Family')
 -- PK of 3 (Brand)
INSERT INTO ProductLevels VALUES ('Brand')
-- PK of 4 (Category)
INSERT INTO ProductLevels VALUES ('Category')
-- PK of 5 (SubCategory)
INSERT INTO ProductLevels VALUES ('SubCategory')
-- PK of 6 (SKU)
INSERT INTO ProductLevels VALUES ('SKU') 

The HierarchyID data type contains methods to Get Descendants, Ancestors, as well as a root definition. So you can use the following functions as part of building an “API” for inserting into/retrieving from a hierarchical data type.

hierProductParentID.GetDescendant(@LastChild,NULL)
hierarchyid::GetRoot()
GetAncestor(1)

With an API for inserting, you can make creating data very easy.

exec InsertProductTree  null,'All Products', 1 

exec InsertProductTree  1 ,'Family A', 2 
exec InsertProductTree  1 ,'Family B', 2 
exec InsertProductTree  1 ,'Family C', 2 

declare @TempParent int = 
 (SELECT ProductTreePK FROM ProductTree WHERE 
   Description = 'Family A') 

exec InsertProductTree 
     @TempParent ,'Family A - Brand AA', 3

exec InsertProductTree  
     @TempParent ,'Family A - Brand AAA', 3

Then, you can run a query using GetAncestor to determine each parent and list Stored Hierarchical Data.