Hierarchyid DataType in SQL Server

Hierarchyid DataType in SQL Server

SQL Server 2012 will introduced new data type call hierarchyid. This data type makes it easier to store and query hierarchical data.This does not automatically represent a tree you have to used hierarchyid function as follows, 

use tempdb
GO
IF OBJECT_ID('EmployeeWithHierarchyID') IS NOT NULL BEGIN
DROP TABLE EmployeeWithHierarchyID;
END
GO
CREATE TABLE EmployeeWithHierarchyID
(
    [Id] hierarchyid not null primary key,
    [Name] nvarchar(50) not null
)
GO
INSERT INTO EmployeeWithHierarchyID ([Id], [Name]) VALUES
    (hierarchyid::GetRoot(), 'General Manager'),
    ('/1/', 'Manager'),
    ('/1/1/', 'Assistant Manager')
GO
SELECT
    Id,
    Id.ToString() AS [Path],
    Id.GetLevel() AS [Level],
    Name
FROM EmployeeWithHierarchyID
GO