HierarchyID type application in SQL Server 2008

An important new feature of SQL Server 2008. The main problem to solve is the table with hierarchical relationships. For example, we use the most organization chart in our daily life. We typically save employee data with a Employees table, and each employee may have a corresponding supervisor. Before you get all the superiors of an employee, or all the subordinates, the usual approach is recursion. The CTE that SQL Server 2005 begins to support facilitates the implementation of this work from a certain program.

But the HierarchyID of SQL 2008 makes the job simpler and more straightforward. The type is actually a CLR custom data type.

Generally we use the time, as in the following example

--Create a table

CREATE TABLE Employees

(

org_id HierarchyID not NULL,

EmployeeId INT not NULL,

EmployeeName VARCHAR (m) not NULL,

Title VARCHAR not NULL

)

Go

--Insert some employees, note the format of the first column, must use/start and end. This is the symbol for a path. This is a key

INSERT into dbo. Employees VALUES ('/', 10000, ' Chen Xizhang ', ' CEO ');

INSERT into dbo. Employees VALUES ('/1/', 10001, ' John ', ' CTO ');

INSERT into dbo. Employees VALUES ('/2/', 10002, ' Dick ', ' CFO ');

INSERT into dbo. Employees VALUES ('/1/1/', 10003, ' Harry ', ' IT Manager ');

INSERT into dbo. Employees VALUES ('/1/2/', 10004, ' Zhao Liu ', ' Manager ');

INSERT into dbo. Employees VALUES ('/1/1/1/', 10005, ' Hong Qi ', ' Employee ');

--View all employees

SELECT * FROM dbo. Employees

--View all employees and their levels

SELECT *,org_id.getlevel () as level from Employees

--View all subordinates of Chen Xizhang

DECLARE @BOSS HierarchyID

SELECT @BOSS =org_id from Employees WHERE employeeid=10000

SELECT *,org_id.getlevel () as level from Employees WHERE org_id.isdescendantof (@BOSS) =1

--View Zhao Liu and all its superiors

DECLARE @Employee HierarchyID

SELECT @Employee =org_id from Employees WHERE employeeid=10004

SELECT *,org_id.getlevel () as level from Employees WHERE @Employee. Isdescendantof (org_id) =1

Some functions related to HierarchyID are mainly

GetAncestor: Getting ancestors of a certain level

Getdescendant: Get a descendant of a certain level

Getlevel: Getting the Grade

Getroot: Get Root

Isdescendantof: Determines whether a node is a descendant of a node

Parse: Converts a string to a HierarchyID. The format of the string is usually/1/.

Read:

Read reads the binary representation of the Sqlhierarchyid from the incoming BinaryReader and sets the Sqlhierarchyid object to that value. Cannot use Transact-SQL call Read. Please use CAST or CONVERT instead.

Getreparentedvalue: Can be used to move nodes (or subtrees)

ToString: Converts HierarchyID to strings, just as opposed to parse

Write

Write writes the binary representation of the Sqlhierarchyid into the incoming binarywriter. Write cannot be invoked by using Transact-SQL. Please use CAST or CONVERT instead.