Egy érdekes típussal kezdünk most ismerkedni, amelyet kissé nehezen sikerült felfognom, tudjuk ezt be a koromnak. :)

HierarchyId. Mit sugall ez a név? Ő egy olyan típus, amely egy hierarchia, azaz egy fa egy adott pontját tudja megcímezni. Hogyan lehet relációs adatbázisban fát építeni? Pl. rekurzív, önhivatkozó táblával, mint a Northwind adatbázis Employees táblája, vagy az AdventureWorks adatbázis HumanResources.Employee táblája. Ez utóbbiban a ManagerID oszlop mutat a főnök EmployeeID-jára.

Az így felépített fa tetszőleges eleme jellemezhető egy úgynevezett OrdPath-szal. Ebben a gyermekelemeknek sorrendje van, mint pl. az xml infosetben, így a gyerekek megcímezhetők a szülő alatt sorszámukkal. 1/2/4 pl. a gyökér node 2. gyermekének a 4. gyerekét jelenti. Az előbbi linken vizualizálják is, érdemes megnézni.

Update: a link időnként nem elérhető, ezért tükröztem a doksit.

No, a HierarchyId egy olyan CLR típus (ez az első alkalom, hogy egy típust .NET-ben írt meg az MS), amely egy OrdPath-ot képes letárolni. A doksi ezt így nem írja le, de így talán könnyebb megérteni. Segítségével tulajdonképpen igen kompakt módon le lehet tárolni egy hierarchia node helyét egy fában. Normál esetben pl. rekurzív CTE-vel járhatunk be egy hierarchiát, hogy meghatározzuk az elérési útját egy node-nak. Ez elég lassú persze, minden szinthez kell egy JOIN. Egy táblában HierarchyId oszlop segítségével minden egyes, a fa egy node-ját reprezentáló sorhoz letárolhatjuk a sor mint fa-node a hierarchiában elfoglalt helyét, így rekurzió nélkül is azonnal látható, hol foglal helyet a hierarchiában az adott sor (mint node).

A HierarchyId felfogható egyfajta denormalizálási technikának is, hisz a hierarchia letárolható a már említett relációs módon is. Akár egyszerre is lehet használni a kettőt, de külön-külön is. Vannak esetek, amikor az egyik lesz hatékonyabb, van, amikor a másik. Ezeket majd későbbi részekben kielemzem, most egyelőre lássunk egyáltalán egy példát, mi a csuda ez a típus.

Induló adatokat úgy gyártok, hogy fogom az HumanResources.Employee táblát, ami relációs módon tárol szervezeti hierarchiát, és ezt átalakítom HierarchyId reprezentációra (a példa a BOL-beli minta alaján készült).

Készítsünk egy keskeny táblát, hogy ne zavarjon meg minket a sok adat:

select EmployeeID, LoginID, ManagerID, Title 
into HumanResources.EmployeeDemo 
from HumanResources.Employee;

Mit találunk benne?

SELECT 
Mgr.EmployeeID AS ManagerID, 
Mgr.LoginID AS Manager,
--Mgr.Title as ManagerTitle,
 
Emp.EmployeeID AS EmployeeID, 
Emp.LoginID as Employee 
--Emp.Title as EmployeeTitle

FROM HumanResources.EmployeeDemo AS Emp
LEFT JOIN HumanResources.EmployeeDemo AS Mgr
ON Emp.ManagerID = Mgr.EmployeeID
ORDER BY ManagerID, EmployeeID

ManagerID   Manager                        EmployeeID  Employee
----------- ------------------------------ ----------- ------------------------------
NULL        NULL                           109         adventure-works\ken0
3           adventure-works\roberto0       4           adventure-works\rob0
3           adventure-works\roberto0       9           adventure-works\gail0
...
3           adventure-works\roberto0       270         adventure-works\sharon0
6           adventure-works\david0         2           adventure-works\kevin0
6           adventure-works\david0         46          adventure-works\sariya0
...
6           adventure-works\david0         272         adventure-works\mary2
7           adventure-works\jolynn0        37          adventure-works\simon0
...

Látható, hogy a 109-es sorszámú ken0 a főgyökér, a legnagyobb főnök. A 3-as kisfőnök beosztottjai 4, 9. stb. számú dolgozók.

No, hozzunk létre egy új táblát, ami ugyanezen adatokat tartalmazza, de most lesz minden node-ra egy közvetlen id-nk is:

create table HumanResources.NewOrg
(
  OrgNode hierarchyid,
  EmployeeId int,
  LoginID nvarchar(50),
  ManagerID int, 
  Title nvarchar(100) 
  constraint PK_NewOrg_OrgNode primary key clustered (OrgNode)
)

Az OrgNode nevű oszlop lett HierarchyId adattípussal pluszként felvéve.

Most jön az adatok átbűvölése. A feladat, hogy rekurzívan járjuk be a hierarchiát, és menet közben generáljuk le a HierarchiId-ket. Azonban emlékezzünk rá, hogy az OrdPath sorrenddel dolgozik (benne van a nevében is, Order Path), így be kellene vezetni valamiféle sorszámozást, amely valamely (tetszőleges) szempont szerint sorbarendezi egy szülő (főnök) gyermekeit (beosztottak). Nem nagy ügy, köszönhetően a 2005-ben megjelent row_number függvénynek. Egy segédtáblában rakjuk össze a sorszámokat:

CREATE TABLE #Children 
(
  EmployeeId int,
  ManagerID int,
  Num int
);
go

create clustered index tmpind 
on #Children(ManagerID, EmployeeID); 
go

insert #Children (EmployeeID, ManagerID, Num)
select EmployeeID, ManagerID, row_number() 
over (partition by ManagerID order by EmployeeID) 
from HumanResources.EmployeeDemo;
go

Főnökök (partition by ManagerID) szerint csoportosítva generáljuk a sorszámokat a beosztottakhoz, EmpoyeeID szerint sorbarendezve (order by EmployeeID). Valójában tök mindegy, mi szerint rendezünk, hisz az eredeti táblában nem is volt sorrend, ne felejtsük el, a relációs adatbázisok halmazokkal, és nem sorrendezett adatokkal dolgoznak. Most viszont az OrdPath miatt ki kell alakítani valami sorrendet.
Kukkantsunk bele a generált táblába:

select ManagerId, EmployeeID, Num 
from #Children
order by ManagerId, Num

ManagerId   EmployeeID  Num
----------- ----------- -----------
NULL        109         1
3           4           1
3           9           2
3           11          3
3           158         4
3           263         5
3           267         6
3           270         7
6           2           1
6           46          2
6           106         3

Az első sor HierarchyId-je / lenne, ő a gyökér. A második sor HierarchyId-ját nem tudjuk kapásból megmondani, mert ahhoz vissza kellene menni, hogy ki a 3-as ember főnöke, egészen a gyökérig visszalépdelve, és akkor a sorrend alapján már lehetne képezni egy OrdPath-ot. Nos, itt az ideje, hogy legeneráljuk az id-kat, egy rekurzív CTE-vel:

with paths(Path, EmployeeID) 
as 
(
--Gyökér
select 
cast('/' as hierarchyid) as OrgNode, 
EmployeeID 
from #Children
where ManagerID is null 

union all

--A gyökér alatti összes szint
select 
cast(p.Path.ToString() + cast(c.Num as varchar(30)) + '/' AS hierarchyid), 
c.EmployeeID
from #Children as c
join Paths as p 
on c.ManagerID = p.EmployeeID 
)
insert HumanResources.NewOrg 
(OrgNode, O.EmployeeID, O.LoginID, O.ManagerID, O.Title)
select P.path, O.EmployeeID, O.LoginID, O.ManagerID, O.Title
from HumanResources.EmployeeDemo as O 
join Paths as P
on O.EmployeeID = P.EmployeeID;
go

A HierarchyId-t ez a sor rakja össze:

cast(p.Path.ToString() + cast(c.Num as varchar(30)) + '/' AS hierarchyid)

Fogjuk a szülő elérési útját (p.Path), átalakítjuk stringgé (.ToString(), elég .NET-es? :), hozzáfűzzük a gyerek sorszámát és egy záró / jelet (+ cast(c.Num as varchar(30)) + ‘/’). Az eredő stringet vissza cast-oljuk HierarchyId-vá. Lehetne másképp is, mert van pár szülő-gyerek kezelő metódusa a típusnak, de erről majd egy másik részben írok.

Aki nem látott még CTE-et, a union feletti rész egyszer fut le, az generálja le a gyökér sort, a alatta levő rész pedig mindig az előző lépés által generált gyerek sorokra hajtódik végre, amíg be nem járja az összes szintet. Ez ugye egy rekurzív self-join.

Nézzünk bele a műbe:

SELECT OrgNode.ToString() AS LogicalNode, * 
FROM HumanResources.NewOrg 
ORDER BY LogicalNode;

LogicalNode                    OrgNode                          EmployeeId  LoginID                        ManagerID   Title
------------------------------ -------------------------------- ----------- ------------------------------ ----------- ------------------------------
/                              0x                               109         adventure-works\ken0           NULL        Chief Executive Officer
/1/                            0x58                             6           adventure-works\david0         109         Marketing Manager
/1/1/                          0x5AC0                           2           adventure-works\kevin0         6           Marketing Assistant
/1/2/                          0x5B40                           46          adventure-works\sariya0        6           Marketing Specialist
/1/3/                          0x5BC0                           106         adventure-works\mary0          6           Marketing Specialist
/1/4/                          0x5C20                           119         adventure-works\jill0          6           Marketing Specialist
/1/5/                          0x5C60                           203         adventure-works\terry0         6           Marketing Specialist
/1/6/                          0x5CA0                           269         adventure-works\wanida0        6           Marketing Assistant
/1/7/                          0x5CE0                           271         adventure-works\john5          6           Marketing Specialist
/1/8/                          0x5D10                           272         adventure-works\mary2          6           Marketing Assistant
/2/                            0x68                             12          adventure-works\terri0         109         Vice President of Engineering
/2/1/                          0x6AC0                           3           adventure-works\roberto0       12          Engineering Manager
/2/1/1/                        0x6AD6                           4           adventure-works\rob0           3           Senior Tool Designer
/2/1/2/                        0x6ADA                           9           adventure-works\gail0          3           Design Engineer
/2/1/3/                        0x6ADE                           11          adventure-works\jossef0        3           Design Engineer
/2/1/4/                        0x6AE1                           158         adventure-works\dylan0         3           Research and Development Manag
/2/1/4/1/                      0x6AE158                         79          adventure-works\diane1         158         Research and Development Engin
/2/1/4/2/                      0x6AE168                         114         adventure-works\gigi0          158         Research and Development Engin

Szép, mi? De mi a csudára jó ez? Miért jó, hogy meg van minden sor OrdPath-ja (HierarchyId-je)? A következő részből kiderül. Stay tuned. :)

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.