Este nuevo tipo de dato fue introducido en la versión SQL Server 2008 para facilitar el almacenamiento y la consulta de datos jerárquicos; además, de traer en una forma compacta que disminuye el numero de bytes requeridos para almacenar un nodo.

Ejemplo:

1. Creación tabla con estructura jerárquica:

CREATE TABLE [dbo].[CCCSubReasons](
[ID] [int] IDENTITY(1,1)NOTNULL,
[ParentID] [hierarchyid] NOTNULL,
[Level] int,
[Name] [varchar] (50)NOTNULL,)

2. Adición campo Level:

Esto lo hacemos para almacenar y saber automátiamente la profundidad de un registro.

Alter Table dbo.CCCFreeSubReasons
add [Level] As ParentID.GetLevel()

3. Inserción del nodo raíz:

insert into CCCFreeSubReasons3(parentid, name)
VALUES (hierarchyid::GetRoot(),'Submotivos')

4. Una vez se ha establecido el nodo raíz, se podrán insertar los nodos hijos a través del siguiente procedimiento almacenado:

CREATE PROCEDURE FreeSubReasons
@ParentID int,
@Name varchar (50)
AS
BEGIN
DECLARE @Manager hierarchyID, @Parent hierarchyID
SELECT @Manager = ParentID
FROM CCCFreeSubReasons
WHERE ID = @ParentID
SELECT @Parent =max(ParentID)
FROM CCCFreeSubReasons
WHERE ParentID.GetAncestor(1)= @Manager

INSERT INTO CCCFreeSubReasons (Name, ParentID)
VALUES (@Name, @Manager.GetDescendant(@Parent,null))
END
GO
exec FreeSubReasons 1,'Mantenimiento backbone'
exec FreeSubReasons 1,'Falla backbone'
exec FreeSubReasons 2,'Carrier'
exec FreeSubReasons 4,'Evento programado'

5. Por último,  se puede realizar la consulta:

select ID, ParentID, Name, ParentID.ToString()'SubReason Path' 
from CCCFreeSubReasons
order by 5