I am Berke

Az utóbbi időben igen sok esetben kellett hierarchikus adatokkal foglalkoznom, illetve interjúk során is fel szoktam tenni ezzel kapcsolatban kérdéseket. Az alábbiakban szeretném bemutatni, hogy a különböző SQL Server verziók esetében ezen adattípusokat hogyan lehet/érdemes kezelni.

Mi lehet hierarchikus adat?

Ezt az egyszerű kérdést talán a legjobban egy példával lehet megválaszolni: ilyen adat lehet egy családfa vagy egy vállalat szervezeti felépítése. Maga a hierarchia jelentése a Wikipedia szerint:

A hierarchia (görögül: hierarchia (ἱεραρχία) (….)) olyan elrendezése elemeknek (tárgyak, nevek, értékek, kategóriák, stb.), amelyben az elemek egymáshoz képest alul, felül vagy egy szinten helyezkednek el.

Absztraktabb módon: hierarchia egyszerűen egy rendezett halmaz vagy egy aciklikus irányított gráf.

Alapvetően 3 féle módon tárolhatunk hierarchikus adatokat:

• Saját magára hivatkozó (SELF JOIN) tábla segítségével: ez működik SQL 2005 előtti verziókkal is, de csak az SQL Server 2005 és újabb verziók támogatják a rekurzív lekérdezéseket. Ennek a funkciónak  a hiánya komplex, nehezen karbantartható lekérdezésekhez vezethet.
• XML adattípus használatával: csak SQL Server 2005 és újabb verziók esetén.
• hierarchyid adattípussal: csak SQL Server 2008 és újabb verziók esetén érhető el ez az adattípus.

A három módot egy példán keresztül mutatnám be: egy cég szervezeti feléítését kell eltárolni, ahol több eseményre és információra vagyok kíváncsi:

• Szeretném megtudni, hogy egy-egy személy hanyadik szinten van a szervezeti felépítésben.
• Mi történik akkor, ha valakit áthelyeznek? Itt egy másik kérdés is felmerül: ki veszi át a helyét, illetve szervezetileg hozzátartozó személyek is mennek vagy nem?

Saját magára hivatkozó (SELF JOIN) tábla

Talán ez a legegyszerűbb megvalósítása a hierarchikus adatok tárolásának, de nem feltétlenül a legoptimálisabb is. Talán a legnagyobb előnye, hogy az SQL Server 2005 előtti verziókkal is működik. Az alábbi tábla struktúra egy példa az ilyen típusú adatok tárolásához

CREATE TABLE [dbo].[EmployeeSelfJoin]
(
	[EmployeeID] int NOT NULL PRIMARY KEY,
	[ManagerID] int NULL REFERENCES [dbo].[EmployeeSelfJoin]([EmployeeID]),
	[EmployeeName] nvarchar(255)
			
);
GO

Szeretném megtudni, hogy ki, melyik szinten helyezkedik el:

WITH OrgCTE AS
(
	SELECT 
		[EmployeeID],
		[ManagerID],
		[EmployeeName],
		1 AS [Level]
	FROM 
		[dbo].[EmployeeSelfJoin]
	WHERE
		[ManagerID] IS NULL
UNION ALL
	SELECT
		A.[EmployeeID],
		A.[ManagerID],
		A.[EmployeeName],
		B.[Level] + 1
	FROM
		[dbo].[EmployeeSelfJoin] A
	JOIN
		OrgCTE B ON A.[ManagerID] = B.[EmployeeID]
)

SELECT * FROM OrgCTE ORDER BY [Level] ASC;

Ez gyakorlatilag a GetLevel() függvénynek felel meg a hierarchyid típusnál. Mi történik akkor, ha valakit áthelyeznek?

A hierarchiában való mozgatásnak 2 esete van:

1. Az alárendelt elemek is mennek a mozgatott elemmel,
2. Az alárendelt elemek nem mennek a mozgatott elemmel, másik elem alárendeltségébe kerülnek vagy nem .

Az első esetben egyszerű a dolgunk mivel csak egy helyen – ManagerID – kell változtatni az adatokon:

UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 1
WHERE [EmployeeID] = 160;

A második esetben már nem olyan egyszerű a helyzet. Ez a fajta módszer nem engedi meg, hogy “Manager” nélkül maradjanak elemek, így itt már 2 lépésben van lehetőség a változást megcsinálni: a sorrend ahogyan csináljuk csak az alkalmazástól és az üzleti szabályoktól függ (+ tranzakciós szint!). Első lépésben az alárendelt elemeket rendezem át, majd ugyan úgy, mint az első esetben a kiválasztott elemet helyezem át:

UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 26
WHERE [ManagerID] = 160;

UPDATE [dbo].[EmployeeSelfJoin] SET [ManagerID] = 1
WHERE [EmployeeID] = 160;

Konvertálás egyik típusból a másikba

A fenti példákat az AdventureWorks2008R2 adatbázis HumanResources.Employee táblájával készítettem el. Az első esetben hierarchyid típusból kellett SELF JOIN típusú táblába adatokat töltenem. Az adatokat az alábbi lekérdezéssel lehetett előállítani:

SELECT
	P.[BusinessEntityID],
	NULL,
	P.[LastName] + ' , ' + P.[FirstName]
FROM
	[HumanResources].[Employee] E
JOIN
	[dbo].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
WHERE
	 E.[OrganizationNode].GetAncestor(1) IS NULL
UNION
SELECT
	P.[BusinessEntityID],
	MGR.[BusinessEntityID],
	P.[LastName] + ' , ' + P.[FirstName]
FROM
	[HumanResources].[Employee] E
JOIN
	[dbo].[Person] P ON E.[BusinessEntityID] = P.[BusinessEntityID]
CROSS APPLY
	(SELECT [BusinessEntityID] FROM [HumanResources].[Employee] WHERE
	 [OrganizationNode] = E.[OrganizationNode].GetAncestor(1)) AS [MGR];

A teljes script innen letölthető: sqlhierarchy-selfjoin.sql (2.80 kb)