storing/inserting hierarchical type data in SQL Server

SQL Server 2008 has new data type HIERARCHYID for managing and inserting hierarchical data. Best example we will get when think about hierarchical data is Employees table.. Used to save the employees data in parent-child columns and using self-join or CTEs we commonly display the data to end user / application in hierarchical manner.

SQL Server 2008 has introduced a new feature called as HIERARCHYID data type.. I would like to show you how to define a HIERARCHYID column and insert data at ROOT level as follows:

CREATE TABLE Employees
(
[HierarchyID] hierarchyid  PRIMARY KEY CLUSTERED,
EmployeeID int UNIQUE NOT NULL,
EmployeeName varchar(20) NOT NULL,
Title varchar(50) NULL
) ;
GO

INSERT INTO Employees ([HierarchyID], EmployeeID, EmployeeName, Title)
VALUES (hierarchyid::GetRoot() , 1,'Nick','CEO')
 
SELECT [HierarchyID].ToString() as HierarchyIDString , [HierarchyID], EmployeeID, EmployeeName, Title
FROM Employees

Output:

HierarchyIDString	HierarchyID	EmployeeID	EmployeeName	Title
/ 	               (blank)           1	        Nick            CEO