I wasn't aware of this addition to SQL 2008 until I read your article. It's interesting but somewhat kludgey.
I played around with hierarchyid after reading this article and discovered it can be surprising easy to mess up records. Specifically, the GetReparentedValue doesn't seem to be very useful if you are moving children from one parent to another parent that already has children. It will actually assign duplicate hierarchyid's.
Here's an example of it happening.
USE [Test]
GO
/****** Object: Table [dbo].[hier_test1] Script Date: 08/27/2009 11:14:30 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hier_test1]') AND type in (N'U'))
DROP TABLE [dbo].[hier_test1]
GO
USE [Test]
GO
/****** Object: Table [dbo].[hier_test1] Script Date: 08/27/2009 11:14:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[hier_test1](
[record_id] [int] IDENTITY(1,1) NOT NULL,
[data1] [nvarchar](50) NULL,
[data2] [nvarchar](2000) NULL,
[hier_id] [hierarchyid] NOT NULL,
[position] [nvarchar](50) NULL,
CONSTRAINT [PK_hier_test1] PRIMARY KEY CLUSTERED
(
[record_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Test.dbo.hier_test1(
data1,
data2,
hier_id
)VALUES(
'/',
'This is the root record',
hierarchyid::GetRoot()
)
exec AddHierRecord 1, '/1', 'Child of Root'
exec AddHierRecord 1, '/2', 'Child of Root'
exec AddHierRecord 1, '/3', 'Child of Root'
exec AddHierRecord 2, '/1/1', 'Child of Child 1'
exec AddHierRecord 2, '/1/2', 'Child of Child 1'
exec AddHierRecord 2, '/1/3', 'Child of Child 1'
exec AddHierRecord 3, '/2/1', 'Child of Child 2'
exec AddHierRecord 3, '/2/2', 'Child of Child 2'
exec AddHierRecord 3, '/2/3', 'Child of Child 2'
exec AddHierRecord 4, '/3/1', 'Child of Child 3'
exec AddHierRecord 4, '/3/2', 'Child of Child 3'
exec AddHierRecord 4, '/3/3', 'Child of Child 3'
exec AddHierRecord 5, '/1/1/1', 'Child of Child 1 of Child 1'
exec AddHierRecord 5, '/1/1/2', 'Child of Child 1 of Child 1'
exec AddHierRecord 5, '/1/1/3', 'Child of Child 1 of Child 1'
exec AddHierRecord 6, '/1/2/1', 'Child of Child 2 of Child 1'
exec AddHierRecord 6, '/1/2/2', 'Child of Child 2 of Child 1'
exec AddHierRecord 6, '/1/2/3', 'Child of Child 2 of Child 1'
select *, hier_id.ToString()
fromhier_test1
declare @oldparent hierarchyid
declare @newparent hierarchyid
select @oldparent = hier_id
fromhier_test1
whererecord_id = 2
select @newparent = hier_id
fromhier_test1
whererecord_id = 3
update hier_test1
sethier_id = hier_id.GetReparentedValue(@oldparent, @newparent)
wherehier_id.IsDescendantOf(@oldparent) = 1
and record_id 2
select *, hier_id.ToString()
fromhier_test1
Look at record_id's 5 and 8 in the second set of results. Then there's the issue of the "grandchildren" records. It does attempt to move these. When it does, however, it becomes impossible to distinguish which of two parents they belong to.
I've gone through the tutorials and documentation on TechNet and they don't offer any examples of moving a branch into an already populated branch.
If anyone can tell me I'm doing something wrong I'd really like to know what it is so I can make use of this new feature.