Hierarchy ID

create table #region
(
nodeID hierarchyid,
regionID int identity(1,1),
regionName varchar(100),
)

-- First row
insert into #region(nodeID,regionName) values(hierarchyid::GetRoot(),'India')
go

-- Second row
declare @rootID hierarchyid = (select hierarchyid::GetRoot() from #region)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(NULL,NULL),'Kerala')

--Third row
declare @firstChild hierarchyid = @rootID.GetDescendant(NULL,NULL)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(@firstChild,NULL),'Tamilnadu')

-- Kerala -> Palakkad
select @rootID = CAST('/1/' as hierarchyid)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(NULL,NULL),'Palakkad')

-- Kerala -> Trivandrum
select @firstChild = @rootID.GetDescendant(NULL,NULL)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(@firstChild,NULL),'Trivandrum')

-- Kerala -> Calicut
declare @secondChild hierarchyid
select @secondChild = @rootID.GetDescendant(@firstChild,NULL)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(@secondChild,NULL),'Calicut')

-- Kerala -> Kannur
declare @thirdChild hierarchyid
select @thirdChild = @rootID.GetDescendant(@secondChild,NULL)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(@thirdChild,NULL),'Kannur')

-- Kerala -> Palakkad -> Mannarkkad
select @rootID = CAST('/1/1/' as hierarchyid)
insert into #region (nodeID,regionName) values(@rootID.GetDescendant(NULL,NULL),'Mannarkkad')


select * from #region
select nodeID.ToString(), regionName from #region

drop table #region