HierarchyID type application in SQL Server 2008

Source: Internet
Author: User
Tags sql 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.

Related Article

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.