İngilizce dilinde oku

Aracılığıyla paylaş

Hiyerarşik veriler (SQL Server)

  • Makale

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıMicrosoft Fabric'de Azure SQL Yönetilen ÖrneğiSQL veritabanı

Yerleşik hierarchyid veri türü, hiyerarşik verileri depolamayı ve sorgulamayı kolaylaştırır. hierarchyid, en yaygın hiyerarşik veri türü olan ağaçları temsil etmek için iyileştirilmiştir.

Hiyerarşik veriler, hiyerarşik ilişkiler tarafından birbiriyle ilişkili bir veri öğeleri kümesi olarak tanımlanır. Bir veri öğesinin başka bir öğenin üst öğesi olduğu hiyerarşik ilişkiler vardır. Veritabanlarında yaygın olarak depolanan hiyerarşik verilere örnek olarak aşağıdaki öğeler verilebilir:

  • Kuruluş yapısı
  • Dosya sistemi
  • Projedeki görev kümesi
  • Dil terimlerinin taksonomisi
  • Web sayfaları arasındaki bağlantıların grafiği

Hiyerarşik yapıya sahip tablolar oluşturmak veya başka bir konumda depolanan verilerin hiyerarşik yapısını açıklamak için veri türü olarak hierarchyid kullanın. Hiyerarşik verileri sorgulamak ve yönetmek için Transact-SQL hierarchyid işlevlerini kullanın.

Anahtar özellikleri

hierarchyid veri türünün değeri, ağaç hiyerarşisindeki bir konumu temsil eder. hierarchyid değerleri aşağıdaki özelliklere sahiptir:

  • Son derece kompakt

    n düğümleri olan bir ağaçtaki bir düğümü temsil etmek için gereken ortalama bit sayısı, ortalama dallanmaya (bir düğümün çocuklarının ortalama sayısı) bağlıdır. Küçük fanoutlar (0-7) için boyut, yaklaşık $6log{A}{n}$ bit'tir; burada A, ortalama fanout değeridir. Ortalama altı düzeye sahip 100.000 kişilik bir kuruluş hiyerarşisindeki düğüm yaklaşık 38 bit alır. Bu, depolama için 40 bit veya 5 bayt olarak yuvarlanır.

  • Karşılaştırma ilk sıradadır

    Verilen iki hierarchyid değeri a ve biçin, a < b, a'in b'den önce ağacın derinlik öncelikli geçişinde geldiği anlamına gelir. hierarchyid veri türlerindeki dizinler derinliğine öncelikli sıraya göredir ve derinliğine öncelikli geçiş sırasında birbirine yakın düğümler birbirine yakın bir şekilde depolanır. Örneğin, bir kaydın alt öğeleri bu kaydın yanında depolanır.

  • Rastgele ekleme ve silme desteği

    GetDescendant (Veritabanı Altyapısı) yöntemini kullanarak, belirli bir düğümün sağında, solunda veya iki eşdüzey arasında yeni bir eşdüzey üretmek her zaman mümkündür. Hiyerarşiden rastgele sayıda düğüm eklendiğinde veya silindiğinde karşılaştırma özelliği korunur. Çoğu ekleme ve silme işlemi sıkıştırma özelliğini korur. Ancak, iki düğüm arasındaki eklemeler biraz daha az yoğun bir temsil ile hierarchyid değerleri oluşturur.

Sınırlama

hierarchyid veri türü aşağıdaki sınırlamalara sahiptir:

  • hierarchyid türünde bir sütun otomatik olarak bir ağacı temsil etmez. Satırlar arasındaki istenen ilişkiyi değerlere yansıtacak şekilde hierarchyid değerlerini oluşturmak ve atamak, uygulamanın sorumluluğundadır. Bazı uygulamalar, başka bir tabloda tanımlanan hiyerarşideki konumu gösteren hierarchyid türünde bir sütuna sahip olabilir.

  • hierarchyid değerleri oluşturma ve atamada eşzamanlılığı yönetmek uygulamaya bağlıdır. Uygulama benzersiz bir anahtar kısıtlaması kullanmadığı veya kendi mantığı aracılığıyla benzersizliği zorlamadığı sürece bir sütundaki hierarchyid değerlerinin benzersiz olması garanti değildir.

  • hierarchyid değerleriyle temsil edilen hiyerarşik ilişkiler, yabancı anahtar ilişkisi gibi zorunlu tutulmaz. A'ın Bgibi bir alt öğeye sahip olduğu ve A'nin silinmesiyle birlikte B'ün var olmayan bir kayda ilişkili olarak kaldığı bir hiyerarşik ilişki kurmak mümkündür ve bazen de uygundur. Eger bu davranış kabul edilemezse, uygulamanın ebeveyn öğeleri silmeden önce alt öğeleri sorgulaması gerekir.

Hierarchyid'ye alternatifler ne zaman kullanılır?

Hiyerarşik verileri temsil etmek için hierarchyid iki alternatifi şunlardır:

  • Ebeveyn/çocuk
  • XML

hierarchyid genellikle bu alternatiflerden daha üstündür. Ancak, bu makalede ayrıntılı olarak belirtilen ve alternatiflerin büyük olasılıkla üstün olduğu belirli durumlar vardır.

Ebeveyn/çocuk

Ebeveyn/çocuk yaklaşımını kullandığınızda, her satır ebeveyne bir referans içerir. Ebeveyn/çocuk ilişkisinde ebeveyn ve çocuk satırlarını içermek için kullanılan tipik bir tabloyu aşağıdaki tablo tanımlar:

SQL 
USE AdventureWorks2022;
GO

CREATE TABLE ParentChildOrg (
    BusinessEntityID INT PRIMARY KEY,
    ManagerId INT REFERENCES ParentChildOrg(BusinessEntityID),
    EmployeeName NVARCHAR(50)
);
GO

Yaygın işlemler için ebeveyn/çocuk ve hierarchyid karşılaştırması yapma:

  • hierarchyidile alt ağaç sorguları önemli ölçüde daha hızlıdır.
  • hierarchyid ile doğrudan alt sorgularbiraz daha yavaştır.
  • hierarchyidile yaprak olmayan düğümlerin taşınması daha yavaştır.
  • Yaprak olmayan düğümlerin eklenmesi ve yaprak düğümlerinin eklenmesi veya taşınması, hierarchyidile aynı karmaşıklıktadır.

Aşağıdaki koşullar mevcut olduğunda ebeveyn/çocuk ilişkisi üstün olabilir.

  • Anahtarın boyutu kritiktir. Aynı sayıda düğüm için, hierarchyid değeri bir tamsayı ailesi (smallint, int, bigint) değerine eşit veya ondan büyüktür. hierarchyid, üst/alt yapı kullanırken gereken ortak tablo ifadelerine kıyasla önemli ölçüde daha iyi G/Ç ve CPU yerel özelliklerine sahip olduğundan, bu yalnızca nadir durumlarda üst/alt yapıyı kullanmanın bir nedenidir.

  • Sorgular, hiyerarşinin bölümleri arasında nadiren gerçekleştirilir. Başka bir deyişle, sorgular genellikle hiyerarşide yalnızca tek bir noktayı ele alır. Bu gibi durumlarda yer paylaşımı önemli değildir. Örneğin, kuruluş tablosu yalnızca tek tek çalışanların bordrolarını işlemek için kullanıldığında, ebeveyn/çocuk yapısı daha uygundur.

  • Yaprak olmayan alttreler sık sık hareket eder ve performans çok önemlidir. Üst/alt gösterimde, hiyerarşideki bir satırın konumunu değiştirmek tek bir satırı etkiler. hierarchyid kullanımındaki bir satırın konumunu değiştirmek n satırı etkiler; burada n taşınan alt ağaçtaki düğüm sayısıdır.

    Yaprak olmayan alt ağaçlar sık sık taşınıyorsa ve performans önemliyse, ancak taşımaların çoğu hiyerarşinin iyi tanımlanmış bir düzeyindeyse, daha yüksek ve düşük düzeyleri iki hiyerarşiye bölmeyi göz önünde bulundurun. Bu, bütün hamleleri daha yüksek hiyerarşinin yaprak düzeylerine taşımaktadır. Örneğin, bir hizmet tarafından barındırılan Web siteleri hiyerarşisini göz önünde bulundurun. Siteler hiyerarşik bir şekilde düzenlenmiş birçok sayfa içerir. Barındırılan siteler site hiyerarşisindeki diğer konumlara taşınabilir, ancak alt sayfalar nadiren yeniden düzenlenir. Bu, şu şekilde gösterilebilir:

    SQL 
    CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO

XML

XML belgesi bir ağaçtır ve bu nedenle tek bir XML veri türü örneği tam bir hiyerarşiyi temsil edebilir. SQL Server'da bir XML dizini oluşturulduğunda, hiyerarşideki konumu göstermek için hierarchyid değerleri dahili olarak kullanılır.

Aşağıdakilerin tümü doğru olduğunda XML veri türünün kullanılması üst düzey olabilir:

  • Hiyerarşinin tamamı her zaman depolanır ve alınır.
  • Veriler uygulama tarafından XML biçiminde kullanılır.
  • Koşul aramaları son derece sınırlıdır ve performans açısından kritik değildir.

Örneğin, bir uygulama birden çok kuruluşu izlerse, her zaman tüm kuruluş hiyerarşisini depolar ve alırsa ve tek bir kuruluşta sorgulama yapmazsa, aşağıdaki formdaki bir tablo mantıklı olabilir:

SQL 
CREATE TABLE XMLOrg (
    Orgid INT,
    Orgdata XML
);
GO

Hiyerarşik veriler için dizin stratejileri

Hiyerarşik verilerin dizinini oluşturmak için iki strateji vardır:

  • derinlik öncelikli

    Derinlik öncelikli dizin, satırları birbirine yakın bir alt ağaçta depolar. Örneğin, bir yönetici aracılığıyla rapor veren tüm çalışanlar yöneticilerinin kayıtlarına yakın bir şekilde depolanır.

    Derinlik öncelikli dizinde, bir düğümün alt ağacındaki tüm düğümler bir arada bulunur. Bu nedenle derinlik öncelikli dizinler, "Bu klasördeki ve alt klasörlerindeki tüm dosyaları bul" gibi alttre'ler hakkındaki sorguları yanıtlamak için verimlidir

  • Genişlik öncelikli

    Genişlik öncelikli bir dizin, hiyerarşinin her düzeyindeki satırları birlikte depolar. Örneğin, doğrudan aynı yöneticiye rapor veren çalışanların kayıtları birbirine yakın bir yerde depolanır.

    Genişlik öncelikli dizinde, bir düğümün tüm doğrudan alt öğeleri birlikte bulunur. Bu nedenle ilk içerik dizinleri, "Doğrudan bu yöneticiye rapor veren tüm çalışanları bul" gibi anlık çocuklar hakkındaki sorguları yanıtlamak için verimlidir.

Derinlik-öncelikli, genişlik-öncelikli veya her ikisine de sahip olmak ve kümeleme anahtarının (varsa) belirlenmesi, yukarıda belirtilen sorgu türlerinin göreli önemine ve SELECT ile DML işlemleri arasındaki göreli öneme bağlıdır. Ayrıntılı bir dizin oluşturma stratejisi örneği için bkz. Eğitim:hierarchyid Veri Türünü Kullanma.

Dizin oluşturma

GetLevel() yöntemi, genişlik öncelikli sıralama oluşturmak için kullanılabilir. Aşağıdaki örnekte hem içerik öncelikli hem de derinlik öncelikli dizinler oluşturulur:

SQL 
USE AdventureWorks2022;
GO

CREATE TABLE Organization (
    BusinessEntityID HIERARCHYID,
    OrgLevel AS BusinessEntityID.GetLevel(),
    EmployeeName NVARCHAR(50) NOT NULL
);
GO

CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization (OrgLevel, BusinessEntityID);
GO

CREATE UNIQUE INDEX Org_Depth_First
ON Organization (BusinessEntityID);
GO

Örnekler

Bu makaledeki Transact-SQL kod örnekleri, Microsoft SQL Server Örnekleri ve Topluluk Projeleri giriş sayfasından indirebileceğiniz AdventureWorks2022 veya AdventureWorksDW2022 örnek veritabanını kullanır.

Temel örnek

Aşağıdaki örnek, başlamanıza yardımcı olmak için kasıtlı olarak basittir. önce bazı coğrafya verilerini barındıracak bir tablo oluşturun.

SQL 
CREATE TABLE BasicDemo (
    [Level] HIERARCHYID NOT NULL,
    Location NVARCHAR(30) NOT NULL,
    LocationType NVARCHAR(9) NULL
);

Şimdi bazı kıtalar, ülkeler/bölgeler, eyaletler ve şehirler için veri ekleyin.

SQL 
INSERT BasicDemo
VALUES ('/1/', 'Europe', 'Continent'),
    ('/2/', 'South America', 'Continent'),
    ('/1/1/', 'France', 'Country'),
    ('/1/1/1/', 'Paris', 'City'),
    ('/1/2/1/', 'Madrid', 'City'),
    ('/1/2/', 'Spain', 'Country'),
    ('/3/', 'Antarctica', 'Continent'),
    ('/2/1/', 'Brazil', 'Country'),
    ('/2/1/1/', 'Brasilia', 'City'),
    ('/2/1/2/', 'Bahia', 'State'),
    ('/2/1/2/1/', 'Salvador', 'City'),
    ('/3/1/', 'McMurdo Station', 'City');

Verileri seçerek Düzey verilerini anlaşılması kolay bir metin değerine dönüştüren bir sütun ekleyin. Bu sorgu ayrıca sonucu hierarchyid veri türüne göre sıralar.

SQL 
SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

Sonuç kümesi aşağıdadır.

Output 
Converted Level  Level     Location         LocationType
---------------  --------  ---------------  ---------------
/1/              0x58      Europe           Continent
/1/1/            0x5AC0    France           Country
/1/1/1/          0x5AD6    Paris            City
/1/2/            0x5B40    Spain            Country
/1/2/1/          0x5B56    Madrid           City
/2/              0x68      South America    Continent
/2/1/            0x6AC0    Brazil           Country
/2/1/1/          0x6AD6    Brasilia         City
/2/1/2/          0x6ADA    Bahia            State
/2/1/2/1/        0x6ADAB0  Salvador         City
/3/              0x78      Antarctica       Continent
/3/1/            0x7AC0    McMurdo Station  City

Hiyerarşi, dahili olarak tutarlı olmasa da geçerli bir yapıya sahiptir. Bahia tek eyalet. Hiyerarşide Brasilia şehrinin bir eşi olarak görünür. Benzer şekilde, McMurdo İstasyonu'nun bir ana ülkesi/bölgesi yoktur. Kullanıcıların bu hiyerarşi türünün kullanımları için uygun olup olmadığını belirlemesi gerekir.

Başka bir satır ekleyin ve sonuçları seçin.

SQL 
INSERT BasicDemo
VALUES ('/1/3/1/', 'Kyoto', 'City'),
    ('/1/3/1/', 'London', 'City');

SELECT CAST([Level] AS NVARCHAR(100)) AS [Converted Level],
    *
FROM BasicDemo
ORDER BY [Level];

Bu, daha olası sorunları gösterir. /1/3/ebeveyn düzey olmasa bile Kyoto, düzey /1/3/1/ olarak eklenebilir. Hem Londra hem de Kyoto, hierarchyidiçin aynı değere sahiptir. Yine kullanıcıların bu tür bir hiyerarşinin kullanımları için uygun olup olmadığını belirlemesi ve kullanımları için geçersiz olan değerleri engellemesi gerekir.

Ayrıca, bu tablo hiyerarşi '/'üst kısmını kullanmadı. Tüm kıtaların ortak ebeveyni olmadığı için atlandı. Tüm gezegeni ekleyerek bir tane ekleyebilirsiniz.

SQL 
INSERT BasicDemo
VALUES ('/', 'Earth', 'Planet');

Üst/alt öğeden hierarchyid'ye geçiş

Çoğu ağaç ebeveyn/çocuk kullanılarak temsil edilir. hierarchyid kullanarak bir üst/alt yapıdan tabloya geçmenin en kolay yolu, hiyerarşinin her düzeyindeki düğüm sayısını izlemek için geçici bir sütun veya geçici bir tablo kullanmaktır. Üst/alt tabloyu geçirme örneği için bkz. Öğreticisi:hierarchyid Veri Türünü Kullanma 1. ders.

Hierarchyid kullanarak ağaç yönetme

bir hierarchyid sütunu mutlaka bir ağacı temsil etmese de, uygulama bunu kolayca sağlayabilir.

  • Yeni değerler oluştururken aşağıdaki adımlardan birini yapın:

    • Ebeveyn satırındaki son çocuk numarasını takip edin.
    • Son çocuğu hesapla. Bunu verimli bir şekilde yapmak için genişlik öncelikli bir dizin gerekir.

  • Bir kümeleme anahtarının parçası olarak sütunda benzersiz bir dizin oluşturarak benzersizliği zorunlu tutun. Benzersiz değerlerin eklendiğinden emin olmak için aşağıdaki adımlardan birini yapın:

    • Benzersiz anahtar ihlali hatalarını algılayın ve yeniden deneyin.
    • Her yeni alt düğümün benzersizliğini belirleyin ve onu serileştirilebilir bir işlemin parçası olarak ekleyin.

Hata algılamayı kullanma örneği

Aşağıdaki örnekte örnek kod, yeni alt EmployeeId değerini hesaplar ve ardından herhangi bir anahtar ihlali algılar ve yeni satırın EmployeeId değerini yeniden derlemek için INS_EMP işaretçisine döner:

SQL 
USE AdventureWorks;
GO

CREATE TABLE Org_T1 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    OrgLevel AS EmployeeId.GetLevel(),
    EmployeeName NVARCHAR(50)
);
GO

CREATE INDEX Org_BreadthFirst ON Org_T1 (
    OrgLevel,
    EmployeeId
);
GO

CREATE PROCEDURE AddEmp (
    @mgrid HIERARCHYID,
    @EmpName NVARCHAR(50)
)
AS
BEGIN
    DECLARE @last_child HIERARCHYID;

    INS_EMP:

    SELECT @last_child = MAX(EmployeeId)
    FROM Org_T1
    WHERE EmployeeId.GetAncestor(1) = @mgrid;

    INSERT INTO Org_T1 (EmployeeId, EmployeeName)
    SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName;

    -- On error, return to INS_EMP to recompute @last_child
    IF @@error <> 0
        GOTO INS_EMP
END;
GO

Seri hale getirilebilir işlem kullanma örneği

Org_BreadthFirst dizini, @last_child'in bir aralık araması kullanılarak belirlenmesini garanti eder. Uygulamanın denetlemek isteyebileceği diğer hata durumlarına ek olarak, ekleme sonrasında yinelenen anahtar ihlali aynı kimliğe sahip birden çok çalışan ekleme girişimini gösterir ve bu nedenle @last_child yeniden hesaplanması gerekir. Aşağıdaki kod, serileştirilebilir bir işlem içindeki yeni düğüm değerini hesaplar:

SQL 
CREATE TABLE Org_T2 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    LastChild HIERARCHYID,
    EmployeeName NVARCHAR(50)
);
GO

CREATE PROCEDURE AddEmp (
    @mgrid HIERARCHYID,
    @EmpName NVARCHAR(50)
)
AS
BEGIN
    DECLARE @last_child HIERARCHYID;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @last_child = EmployeeId.GetDescendant(LastChild, NULL)
    FROM Org_T2
    WHERE EmployeeId = @mgrid;

    UPDATE Org_T2
    SET LastChild = @last_child
    WHERE EmployeeId = @mgrid;

    INSERT Org_T2 (EmployeeId, EmployeeName)
    VALUES (@last_child, @EmpName);

    COMMIT;
END;

Aşağıdaki kod tabloyu üç satırla doldurur ve sonuçları döndürür:

SQL 
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES (HIERARCHYID::GetRoot(), 'David');
GO

AddEmp 0x, 'Sariya'
GO

AddEmp 0x58, 'Mary'
GO

SELECT * FROM Org_T2

Sonuç kümesi aşağıdadır.

Output 
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

Ağacı zorunlu kılma

Önceki örneklerde, bir uygulamanın bir ağacın bakımının nasıl sağlandığı gösterilmektedir. Kısıtlamalar kullanarak bir ağacı uygulamak için, her düğümün üst öğesini tanımlayan hesaplanan bir sütun, birincil anahtar ID'sine bir yabancı anahtar kısıtlaması ile oluşturulabilir.

SQL 
CREATE TABLE Org_T3 (
    EmployeeId HIERARCHYID PRIMARY KEY,
    ParentId AS EmployeeId.GetAncestor(1) PERSISTED REFERENCES Org_T3(EmployeeId),
    LastChild HIERARCHYID,
    EmployeeName NVARCHAR(50)
);
GO

Hiyerarşik ağacı korumak için güvenilir olmayan kodun tabloya doğrudan DML erişimi olduğunda bu ilişkiyi zorlama yöntemi tercih edilir. Ancak kısıtlamanın her DML işleminde denetlenmesi gerektiğinden bu yöntem performansı düşürebilir.

CLR kullanarak ataları bulma

Hiyerarşideki iki düğümü içeren yaygın bir işlem, en düşük ortak atayı bulmaktır. hierarchyid türü her ikisinde de kullanılabildiğinden, bu görev Transact-SQL veya CLR'de yazılabilir. Performans daha hızlı olduğundan CLR önerilir.

Atasal değerleri listelemek ve en düşük ortak ata bulmak için aşağıdaki CLR kodunu kullanın:

C# 
using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server; // SqlFunction Attribute
using Microsoft.SqlServer.Types;  // SqlHierarchyId

public partial class HierarchyId_Operations
{
    [SqlFunction(FillRowMethodName = "FillRow_ListAncestors")]
    public static IEnumerable ListAncestors(SqlHierarchyId h)
    {
        while (!h.IsNull)
        {
            yield return (h);
            h = h.GetAncestor(1);
        }
    }

    public static void FillRow_ListAncestors(
        Object obj,
        out SqlHierarchyId ancestor
    )
    {
        ancestor = (SqlHierarchyId)obj;
    }

    public static HierarchyId CommonAncestor(
        SqlHierarchyId h1,
        HierarchyId h2
    )
    {
        while (!h1.IsDescendantOf(h2))
        {
            h1 = h1.GetAncestor(1);
        }

        return h1;
    }
}

Aşağıdaki Transact-SQL örneklerde ListAncestor ve CommonAncestor yöntemlerini kullanmak için DLL'yi derleyin ve aşağıdaki örneğe benzer bir kod yürüterek SQL Server'da HierarchyId_Operations derlemesini oluşturun:

SQL 
CREATE ASSEMBLY HierarchyId_Operations
    FROM '<path to DLL>\ListAncestors.dll';
GO

Ataları listele

Bir düğümün atalarının listesini oluşturmak, örneğin bir kuruluştaki konumu göstermek için yaygın bir işlemdir. Bunu yapmanın bir yolu, daha önce tanımlanmış HierarchyId_Operations sınıfını kullanarak table-valued-function kullanmaktır:

Transact-SQL kullanma:

SQL 
CREATE FUNCTION ListAncestors (@node HIERARCHYID)
RETURNS TABLE (node HIERARCHYID)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors;
GO

Kullanım örneği:

SQL 
DECLARE @h HIERARCHYID

SELECT @h = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID,
    OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
INNER JOIN ListAncestors(@h) AS A
    ON ED.OrgNode = A.Node
GO

En düşük ortak atayı bul

Daha önce tanımlanan HierarchyId_Operations sınıfını kullanarak, hiyerarşideki iki düğümü içeren en düşük ortak atası bulmak için aşağıdaki Transact-SQL işlevini oluşturun:

SQL 
CREATE FUNCTION CommonAncestor (
    @node1 HIERARCHYID,
    @node2 HIERARCHYID
)
RETURNS HIERARCHYID
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor;
GO

Kullanım örneği:

SQL 
DECLARE @h1 HIERARCHYID, @h2 HIERARCHYID;

SELECT @h1 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\jossef0';-- Node is /1/1/3/

SELECT @h2 = OrgNode
FROM HumanResources.EmployeeDemo
WHERE LoginID = 'adventure-works\janice0';-- Node is /1/1/5/2/

SELECT OrgNode.ToString() AS LogicalNode, LoginID
FROM HumanResources.EmployeeDemo
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2);

Sonuçta elde edilen düğüm /1/1/

Alt ağaç taşıma

Bir diğer yaygın işlem de alt ağaçları taşımaktır. Aşağıdaki prosedür @oldMgr alt ağacını alır ve bunu, @oldMgrdahil, @newMgr'nin alt ağacı yapar.

SQL 
CREATE PROCEDURE MoveOrg (
    @oldMgr NVARCHAR(256),
    @newMgr NVARCHAR(256)
)
AS
BEGIN
    DECLARE @nold HIERARCHYID, @nnew HIERARCHYID;

    SELECT @nold = OrgNode
    FROM HumanResources.EmployeeDemo
    WHERE LoginID = @oldMgr;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    BEGIN TRANSACTION;

    SELECT @nnew = OrgNode
    FROM HumanResources.EmployeeDemo
    WHERE LoginID = @newMgr;

    SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL)
    FROM HumanResources.EmployeeDemo
    WHERE OrgNode.GetAncestor(1) = @nnew;

    UPDATE HumanResources.EmployeeDemo
    SET OrgNode = OrgNode.GetReparentedValue(@nold, @nnew)
    WHERE OrgNode.IsDescendantOf(@nold) = 1;

    COMMIT TRANSACTION;
END;
GO

İlgili içerik