Hierarchyid Ancestors' Chain

 

 

In this article, I will present a new feature of SQL Server 2008: the HierarchyID data-type. We will see that this new data-type provides a new way to design trees in databases. It adds functionalities to T-SQL language and retrieve the Ancestors' chain string.

 

CREATE TABLE [dbo].[ContentCategories](
[ContentCategoryID] [int] IDENTITY(1,1) NOT NULL,
[ContentCategoryHierarchyId] [hierarchyid] NOT NULL,
[ContentCategoryLevel]  AS ([ContentCategoryHierarchyId].[GetLevel]()),
[ContentCategoryName] [nvarchar](255) NOT NULL,
CONSTRAINT [PK__ContentCategories] PRIMARY KEY CLUSTERED
(
[ContentCategoryID] 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

 

ContentCategoryID	ContentCategoryHierarchyId		ContentCategoryLevel	ContentCategoryName
1	0x	/	0	N/A
4	0x58	/1/	1	Dev
5	0x5AC0	/1/1/	2	Asp.Net
22	0x5AD6	/1/1/1/	3	Membership
6	0x5B40	/1/2/	2	Sql Server
7	0x5B56	/1/2/1/	3	Sql Server 2008
21	0x5B5A	/1/2/2/	3	TSQL
8	0x5BC0	/1/3/	2	Ajax
9	0x5C20	/1/4/	2	JSON
10	0x5C60	/1/5/	2	Oracle
11	0x5C6B	/1/5/1/	3	Oracle 10g
12	0x5C6D	/1/5/2/	3	Oracle 11g
20	0x5C6F	/1/5/3/	3	PLSQL
17	0x5CA0	/1/6/	2	JavaScript
18	0x5CE0	/1/7/	2	EDF
19	0x5D10	/1/8/	2	LINQ
23	0x5D30	/1/9/	2	IIS
24	0x5D3580	/1/9/1/	3	IIS 7
25	0x5D50	/1/10/	2	jQuery
26	0x5D70	/1/11/	2	C#
27	0x5D90	/1/12/	2	Visual Basic 6
28	0x5DB0	/1/13/	2	Troubleshooting
30	0x5DB580	/1/13/1/	3	TFS 2010
29	0x5DD0	/1/14/	2	Visual Studio 2008

 

CREATE Function [dbo].[fnContentCategoriesParentChain]
(
@ContentCategoryId Int
)
RETURNS nvarchar(max)
AS
/*
***
*** NOTE: The root row is identified by the TblKey (PK) and the ParentTblKey
*** being equal
***
*/
BEGIN
DECLARE @ContentCategoryHierarchyID hierarchyid
DECLARE @cats nvarchar(max)
set @cats = N''
SELECT @ContentCategoryHierarchyID = ContentCategoryHierarchyID
FROM ContentCategories
WHERE ContentCategoryID = @ContentCategoryId
SELECT @cats= @cats + ' >> ' + [ContentCategoryName]
--ContentCategoryHierarchyID,
--ContentCategoryHierarchyID.ToString() AS [Path],
--ContentCategoryHierarchyID.GetLevel() AS [Level],
--ContentCategoryHierarchyID.GetAncestor(1),
--[ContentCategoryName]
FROM ContentCategories
WHERE @ContentCategoryHierarchyID.IsDescendantOf(ContentCategoryHierarchyID) = 1
if (@cats like N'>>%')
begin
set @cats=RIGHT(@cats,LEN(@cats) -2)
end
RETURN @cats
END
GO

 

select dbo.fnContentCategoriesParentChain(24)

N/A>>Dev>>IIS>>IIS 7