Microsoft® SQL Server® 2012 Bible

328

Part III: Advanced T-SQL Data Types Querying Techniques

17 0x6AC0 /2/1/ 2 Kevin Brown

18 0x6B40 /2/2/ 2 John Wood

19 0x6BC0 /2/3/ 2 Mary Dempsey

20 0x6C20 /2/4/ 2 Wanida Benshoof

21 0x6C60 /2/5/ 2 Terry Eminhizer

22 0x6CA0 /2/6/ 2 Sariya Harnpadoungsataya

23 0x6CE0 /2/7/ 2 Mary Gibson

24 0x6D10 /2/8/ 2 Jill Williams

25 0x78 /3/ 1 James Hamilton

The results in column 3 are a denormalized representation of the complete ancestry and

hierarchy, but unlike the materialized path pattern, the HierarchyID data type stores

the relative node position.

Select a Single Node

The HierarchyID makes it easy to fi lter the data type in the WHERE clause to return

single rows by using the text form of the data:

SELECT TOP 25

e.BusinessEntityID AS BusEntID,

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

e.JobTitle

FROM HumanResources.Employee e

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

WHERE e.OrganizationNode = '/3/3/'

What makes this nice is that the conversion was implicit, in that you didn’t need to use the

ToString() method to convert the data to text form.

BusEntID Name JobTitle

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

222 A. Scott Wright Master Scheduler

Searching for Ancestors

Searching for ancestors is made easy with the HierarchyID data type via the use of the

IsDescendantOf()method. This method tests specifi ed nodes to determine if those

nodes are a descendant of another node. The IsDescendantOf() method returns true if

nodes are found.

The following example is a two-step process. The fi rst SELECT statement gets the

HierarchyID for a given employee (in this case, A. Scott Wright). Because the variable is

of type HierarchyID, you can easily apply the IsDescendantOf() method in the second

SELECT statement, which is what is applied in the fourth column.

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

http://www.it-ebooks.info