storing/inserting hierarchical type data in SQL Server

Professionaluser
Posted by Professionaluser under Sql Server category on | Points: 40 | Views : 289
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

Comments or Responses

Login to post response