Microsoft® SQL Server® 2012 Bible

326

Part III: Advanced T-SQL Data Types Querying Techniques

This chapter primarily focuses on the HierarchyID data type because it is relatively new,

and there is still much to be learned about it. This chapter briefl y discusses other options

such as Material Path, XML, and CTEs.

The examples for this chapter are taken from the AdventureWorks database for SQL Server

2012, which can be downloaded from Codeplex here:

http://msftdbprodsamples.codeplex.com/releases/view/55330

HierarchyID

The HierarchyID data type was introduced in SQL Server 2008 with the intention to

provide and solve the problems surrounding working with hierarchical data. As a quick

background, the HierarchyID data type is a CLR data type, but enabling the CLR to use

the HierarchyID data type is not needed.

The following query joins the HumanResources.Employee table with the Person

.Person table, including the column OrganizationNode as one of the columns returned

in the query.

SELECT TOP 25

e.BusinessEntityID AS BusEntID,

e.OrganizationNode AS OrgNode,

e.OrganizationLevel AS OrgLevel,

p.FirstName + ' ' + p.LastName AS 'Name'

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

In the following results, notice the data returned in the OrgNode column. The

OrganizationNode column in the HumanResources.Employee table is a HierarchyID

data type. In its raw form, it is simply hex data.

BusEntID OrgNode OrgLevel Name

-------- ------- -------- ------------

1 0x 0 Ken Sánchez

2 0x58 1 Terri Duffy

3 0x5AC0 2 Roberto Tamburello

4 0x5AD6 3 Rob Walters

5 0x5ADA 3 Gail Erickson

6 0x5ADE 3 Jossef Goldberg

7 0x5AE1 3 Dylan Miller

8 0x5AE158 4 Diane Margheim

9 0x5AE168 4 Gigi Matthew

10 0x5AE178 4 Michael Raheem

11 0x5AE3 3 Ovidiu Cracium

12 0x5AE358 4 Thierry D'Hers

c13.indd 326c13.indd 326 7/30/2012 4:47:04 PM7/30/2012 4:47:04 PM

http://www.it-ebooks.info