Index on HierarchyID : Handling Hierarchical data inside the database Part1
Sometimes we need to handle hierarchical data in databases like trees where data elements are having relationship like parent and child. Before SQL Server 2008 it was complicated to deal with such kind of data. Inside SQL Server 2008 Microsoft introduces a new data type Index on HierarchyID to handle such type of data and reduce the complexity. By using this data type you can also use various methods provided by SQL Server to deal with such data. Here we will see how can, we use it:
USE Master go create database HierarchyTest go USE HierarchyTest go create table xtHid ( id HierarchyID, name varchar(20) )
Now insert first data row with below code:
insert into xtHid values(HierarchyID::GetRoot(),'Microsoft') go
Now if you want to see the data inside that table then
Select * from xtHid go
Let’s insert some more data:
declare @count int declare @search varchar(20) declare @insert varchar(20) declare @temp1 hierarchyID declare @temp2 hierarchyID set @count=0 while @count<6 begin if (@count=0) begin set @search='Microsoft' set @insert='SQLServer' end if (@count=1) begin set @search='SQLServer' set @insert='SQLServer2005' end if (@count=2) begin set @search='SQLServer' set @insert='SQLServer2008' end if (@count=3) begin set @search='SQLServer' set @insert='SQLServer2012' end if (@count=4) begin set @search='Microsoft' set @insert='MSOffice' end if (@count=5) begin set @search='MSOffice' set @insert='MSOffice2010' end Select @temp1=id from xtHid where name=@search Select @temp2=max(id) from xtHid where id.GetAncestor(1)=@temp1 Insert into xtHid values(@temp1.GetDescendant(@temp2,NULL),@insert) Set @count=@count+1 end
Now just Select the data from above table:
Select * from xtHid
Here the output of id column is not clear, so we can use ToString() method on id column here to make the output as more readable and understandable:
Select id.ToString() as id, name from xthid
Here the above output is clearer.
Regards
Prince Rastogi
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook