Working with hierarchyid

The hierarchyid data type is variable length data type introduced in SQL Server 2008.

Create table with hierarchyid data type

declare @emp as table(id int,hierid hierarchyid,name varchar(100))

hierarchyid functions.

GetRoot() : GetRoot function is used for getting root level. we can use this function for inserting the data as level 0.

GetDescendant() : function accepts two parameters child1 and child2.

if both are null,null it is in first level.

GetLevel (): function is used for getting hierarchy level.

insert @emp

select 1,hierarchyid::GetRoot(),'malls'

declare @parentid hierarchyid

select @parentid=hierarchyid::GetRoot()

insert @emp

select 2,@parentid.GetDescendant(null,null),'redd'

--select hierid.ToString(),hierid.GetLevel() from @emp

select id,name,hierid.GetLevel() from @emp

--getroot(),GetDescendant,GetLevel,read,write

select id,name,hierid.GetLevel() from @emp

where hierid.GetLevel()=1