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
--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 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 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.