Hiyerarşik veri (SQL Server)
Yerleşik hierarchyidveri türü kolaylaştırır hiyerarşik bir veri deposu ve sorgu. hierarchyidhiyerarşik veri en yaygın türü olan ağaçlar, temsil için optimize edilmiştir.
Hiyerarşik verileri birbirine hiyerarşik ilişkileriyle ilgili veri öğeleri kümesi olarak tanımlanır. Bir veri öğesi başka bir öğeyi üst olduğu hiyerarşik ilişkileri yok. Genellikle veritabanlarında depolanan hiyerarşik veri örnekleri arasında şunlar yer alır:
Bir organizasyon yapısı
Bir dosya sistemi
Projedeki görevleri
Bir taksonomi dil koşulları
Grafik Web sayfaları arasında bağlantılar
Kullanım hierarchyid veri türü ile hiyerarşik bir yapı tablo oluşturun veya başka bir konumda saklanan verileri hiyerarşik yapısını açıklar. Kullanım hierarchyid işlevleri de Transact-SQLsorgulamak ve hiyerarşik verileri yönetmek için.
Bu Konuda
Hierarchyid anahtar özellikleri
Hierarchyid sınırlamaları
Ne zaman hierarchyid kullanma alternatifleri
Hiyerarşik veri için dizin oluşturma stratejileri
ilişkili Görevler
Üst ve alt hierarchyid için geçiş yapma.
Bir ağaç kullanma hierarchyid yönetme
Bir ağaç zorlama
Atalarının clr kullanarak bulma
Öncüleri listeleme
En düşük ortak öncül bulmak
Alt ağaçlar taşıma
Hierarchyid anahtar özellikleri
Değeri hierarchyidveri türünü gösteren bir ağaç hiyerarşisinde bir konuma. İçin değer hierarchyidaşağıdaki özelliklere sahip:
Son derece kompakt
Ortalama ile ağacındaki bir düğümü temsil etmek için gerekli bit sayısı n düğümleri ortalama fanout üzerinde (bir düğümünün ortalama sayısı) bağlıdır. Küçük fanouts, büyüklüğü ise yaklaşık 6 (0-7) için * günlükan bit, a ortalama fanout olduğu yerde. Bir düğümü hiyerarşide bir kuruluş 100.000 kişilik bir ortalama fanout 6 seviyeden yaklaşık 38 bit alır. Bu 40 bit veya depolama için 5 bayt olarak yuvarlanır.
Karşılaştırma derinliği ilk sırada olduğu
İki verilen hierarchyiddeğerlerini bir ve b, bir<b anlamına gelir bir ağaç derinliği ilk geçişi b önce gelir. Dizinler üzerinde hierarchyidveri türleridir derinliği ilk sırada ve düğümler birbirine yakındır derinliği ilk geçişi birbirine depolanır. Örneğin, bir kayıt alt kaydı bitişik depolanır.
Rasgele eklemeler ve silmeler için destek
Kullanarak GetDescendant yöntemi, her zaman belirli bir düğüm sağındaki solundaki herhangi bir belirtilen düğüm veya herhangi bir iki kardeş arasında bir kardeş oluşturmak mümkün olduğunu. Düğüm rasgele sayıda eklenen veya hiyerarşi silinmiş karşılaştırma özelliği korunur. Çoğu eklemeler ve silmeler anlatım özelliği korumak. Ancak, iki düğüm arasında eklemeler hierarchyid değerleri ile daha az kompakt üretecek gösterimi.
[YUKARI]
Hierarchyid sınırlamaları
hierarchyid Veri türü aşağıdaki sınırlamalar vardır:
Bir sütun türü hierarchyidotomatik olarak bir ağaç temsil etmiyor. Oluşturmak ve atamak için uygulamaya kadar öyle hierarchyiddeğerleri böyle bir şekilde istenen ilişki satırları arasında değerler yansıtılır. Bazı uygulamalar-ebilmek-si olmak bir sütun türü hierarchyidbaşka bir tabloda tanımlı hiyerarşisindeki konumunu gösterir.
Oluşturma ve atama müşterek kullanımı yönetmek için uygulama kadar mi hierarchyiddeğerler. Garanti, hierarchyidbir sütundaki değerlerin benzersiz uygulama benzersiz bir anahtar kısıtlaması kullanır veya benzersiz kendisi, kendi mantığı ile zorlar.
Hiyerarşik ilişkileri temsil hierarchyiddeğerler değil bir yabancı anahtar ilişkisi gibi zorunlu. Mümkün ve bazen uygun bir hiyerarşik ilişki burada bir çocuk b a dir ve a b ile bir ilişkisi varolmayan bir kayıt ayrıldıktan sonra silinir. Bu davranışı kabul edilemez ise, uygulamanın anne silmeden önce torunları için sorgulamalısınız.
[YUKARI]
Ne zaman hierarchyid kullanma alternatifleri
İki seçimli-e doğru hierarchyidhiyerarşik verileri temsil edilir:
Üst ve alt
XML
hierarchyidgenellikle bu alternatifler üstündür. Ancak, belirli durumlarda, alternatifleri muhtemel üstün olduğu aşağıda ayrıntılı vardır.
Üst ve alt
Üst ve alt yaklaşım kullanırken, her satırın üst başvuru içeriyor. Aşağıdaki tablo, üst ve alt-üst ilişkisi içindeki alt satırları içermesini kullanılan tipik bir tablo tanımlar:
USE AdventureWorks2012 ;
GO
CREATE TABLE ParentChildOrg
(
BusinessEntityID int PRIMARY KEY,
ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),
EmployeeName nvarchar(50)
) ;
GO
Üst ve alt karşılaştırma ve hierarchyidortak operasyonlar için
Alt ağacı sorguları ile önemli ölçüde daha hızlı hierarchyid.
Doğrudan alt sorguları ile biraz yavaş hierarchyid.
Yaprak olmayan düğümleri Hareketli yavaş ile hierarchyid.
Yaprak olmayan düğüm ekleme ve eklemek ya da taşımak yaprak düğümlerin aynı karmaşıklığı ile olan hierarchyid.
Aşağıdaki koşulların üst ve alt üstün olabilir:
Anahtar boyutu çok önemlidir. Düğümler, aynı sayıda için bir hierarchyiddeğere eşit veya daha büyük bir tamsayı ailesi (smallint, int, bigint) değer. Bu yalnızca ender durumlarda, üst ve alt kullanmak için bir neden çünkü hierarchyidI/o ve cpu karmaşıklığını önemli ölçüde daha iyi yöreden bir üst ve alt yapı kullanırken gerekli ortak tablo ifadeleri daha oldu.
Nadiren hiyerarşi bölümlerde sorgu sorgular. Diğer bir deyişle, sorguları genellikle sadece tek bir noktadan hiyerarşisinde adresi. Bu gibi durumlarda co-location önemli değildir. Örneğin, üst ve alt kuruluş tabloyu yalnızca tek tek çalışanlar için bordro işlemek için kullanıldığında üstündür.
Yaprak olmayan ağaçların sık hareket ve performans çok önemli. Bir üst ve alt gösteriminde bir hiyerarşi içinde bir satırın yeri değişen tek bir satır etkiler. Bir satırın yeri değişen bir hierarchyidkullanımını etkiler n satırlar, nerede n ise alt düğümlerin sayısı taşınıyor.
Yaprak olmayan ağaçların sık taşımak ve performans önemlidir, ama iyi tanımlanmış bir hiyerarşi düzeyinde hamle çoğu, daha yüksek ve daha düşük düzeyde bölme iki hiyerarşileri düşünün. Bu yaprak-düzey üst hiyerarşi içinde tüm hamle yapar. Örneğin, bir hizmet tarafından barındırılan Web siteleri hiyerarşisi düşünün. Siteler, hiyerarşik bir şekilde düzenlenmiş birçok sayfaları içerir. Barındırılan siteler site hiyerarşisinde başka yerlere taşınmış olabilir, ancak alt sayfaları nadiren kavşak. Bu yolu ile temsil edilebilir:
CREATE TABLE HostedSites ( SiteId hierarchyid, PageId hierarchyid ) ; GO
[YUKARI]
XML
Bir ağaç bir xml belgesi olduğunu ve bu nedenle tek bir xml veri türü örneği tam hiyerarşisini temsil edebilir. İçinde SQL Serverxml dizin oluşturulduğunda, hierarchyiddeğerleri dahili olarak hiyerarşisindeki konumunu göstermek için kullanılır.
Tüm aşağıdaki doğru olduğunda xml verileri kullanarak tür üstün olabilir:
Tam hiyerarşi her zaman-saklanan alınır ve.
Verileri xml biçiminde uygulama tarafından tüketilmektedir.
Son derece sınırlıdır ve değil kritik performans yüklemi arar.
Örneğin, bir uygulama Çoklu kuruluşlar izler, her zaman depolar ve tam kuruluş hiyerarşisi alır ve tek bir örgüt haline sorgulamak değil, aşağıdaki biçimde bir tablo mantıklı olabilir:
CREATE TABLE XMLOrg
(
Orgid int,
Orgdata xml
) ;
GO
[YUKARI]
Hiyerarşik veri için dizin oluşturma stratejileri
Hiyerarşik verileri dizin oluşturma için iki strateji vardır:
Derinliği ilk
Derinliği ilk dizin satırları alt ağacın birbirine yakın depolar. Örneğin, rapor Yöneticisi aracılığıyla tüm çalışanların yöneticilerinin saklanan kaydı.
Tüm düğümleri düğüm alt ağaçtaki derinliği ilk dizinde arada. Derinliği ilk dizinler, ağaçların, "tüm dosyaları bu klasör ve alt klasörleri bulmak gibi" hakkındaki sorguları yanıtlamak için bu nedenle verimlidir.
Etki derecesini elde ilk
Etki derecesini elde ilk satırları hiyerarşi her düzeyde birlikte depolar. Örneğin, doğrudan aynı yöneticiye çalışanları birbirine saklanır.
Etki derecesini elde ilk dizin içinde bir düğümün tüm doğrudan çocuk arada. Etki derecesini elde ilk dizin bu nedenle hemen çocuklar, "doğrudan bu yöneticiye tüm çalışanları bulmak gibi" hakkındaki sorguları yanıtlayan için etkilidir.
Derinliği ilk etki derecesini elde ilk, veya hem ve kümeleme anahtarı (varsa), için için yukarıdaki tür sorgu göreli önemi ve seçme Rakip göreli önemi bağlıdır olsun. dml işlemleri. Dizin oluşturma stratejileri detaylı bir örnek için bkz: Öğretici: veri türü hierarchyid kullanma.
[YUKARI]
Örnekler
**GetLevel()**Yöntemi Etki derecesini elde ilk sipariş oluşturmak için kullanılabilir. Aşağıdaki örnekte, ilk genişliği ve derinliği ilk dizinler oluşturulur:
USE AdventureWorks2012 ;
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
[YUKARI]
ilişkili Görevler
Üst ve alt hierarchyid için geçiş yapma.
Çoğu ağaçlar, üst ve alt kullanılarak temsil edilir. Üst ve alt yapıdan bir tablo kullanarak geçirmek için en kolay yolu hierarchyidgeçici bir sütun veya geçici tablo hiyerarşi her düzeydeki düğümlerin sayısını izlemek için kullanmaktır. Üst ve alt tablo geçirme konusunda bir örnek için bkz: Ders 1 Öğretici: veri türü hierarchyid kullanma.
[YUKARI]
Bir ağaç kullanma hierarchyid yönetme
Her ne kadar bir hierarchyidsütun değil mutlaka temsil bir ağaç, bir uygulamayı kolayca o does sağlayabilirsiniz.
Yeni değerler oluşturulurken, aşağıdakilerden birini yapın:
Üst satırdaki son çocuk numarasını izlemek.
Son alt hesaplar. Böylece verimli bir etki derecesini elde ilk dizin gerektirir.
Benzersiz bir dizin sütunu, bir küme anahtarının bir parçası olarak belki yaratarak benzersizliği. Benzersiz değerler eklenir sağlamak için aşağıdakilerden birini yapın:
Benzersiz anahtar ihlali başarısızlıkları algılamak ve yeniden deneyin.
Her yeni alt düğüm benzersizliğini belirleme ve seri hale getirilebilir bir hareketin bir parçası olarak ekleyin.
[YUKARI]
Hata algılama kullanmaya örnek
Aşağıdaki örnek, örnek kod yeni alt hesaplar EmployeeID değer, herhangi bir anahtar ihlali algılar ve döner INS_EMP işaretçisi yeniden EmployeeID yeni satır değeri:
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 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
[YUKARI]
Seri hale getirilebilir bir hareket kullanarak örnek
Org_BreadthFirst bu belirleme sağlar dizin @ DOMNode::last_child aralığı arama kullanır. Bir uygulama, aynı kimliğe sahip birden çok çalışan ekleme denemesi eklemek gösterir sonra yinelenen bir anahtar ihlali kontrol etmek isteyebilirsiniz diğer hata durumlarda ek olarak ve bu nedenle @ DOMNode::last_child yeniden gerekir. Aşağıdaki kod, yeni düğüm değerini hesaplamak için bir seri hale getirilebilir hareket ve Etki derecesini elde ilk dizin kullanır:
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
UPDATE Org_T2
SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL)
WHERE EmployeeId = @mgrid
INSERT Org_T2 (EmployeeId, EmployeeName)
VALUES(@last_child, @EmpName)
COMMIT
END ;
Aşağıdaki kod, üç satır içeren tabloyu doldurur ve sonuçları döndürür:
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 buradadır.
EmployeeId LastChild EmployeeName
---------- --------- ------------
0x 0x58 David
0x58 0x5AC0 Sariya
0x5AC0 NULL Mary
[YUKARI]
Bir ağaç zorlama
Yukarıdaki örnekler, bir uygulama bir ağaç tutulan nasıl emin olabilirim göstermektedir. Bir ağaç kısıtlamaları kullanarak uygulamak için her düğümün üst tanımlar hesaplanmış bir sütun yabancı anahtar kısıtlaması sırt-e doğru birincil anahtar kimliği ile oluşturulabilir.
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ğaç korumak için güvenilir olmayan kod tablo doğrudan dml erişimi olan bir ilişki zorlayarak bu yöntemi tercih edilir. Ancak bu yöntem her dml işlemi kısıtlamayı denetlenmesi gerektiğinden performansını düşürebilir.
[YUKARI]
Atalarının clr kullanarak bulma
En düşük ortak öncül bulmak için bir hiyerarşi içinde iki düğüm içeren ortak bir operasyon olduğunu. Bu ikisini de yazılabilir Transact-SQLveya clr, çünkü hierarchyidtürü de kullanılabilir. clr performans daha hızlı olacağı için önerilir.
Liste öncüleri ve en düşük ortak öncül bulmak için aşağıdaki clr kodu kullanın:
using System;
using System.Collections;
using System.Text;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Types;
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.IsDescendant(h2))
h1 = h1.GetAncestor(1);
return h1;
}
}
Kullanmak için ListAncestor ve CommonAncestor yöntemleri aşağıdaki Transact-SQLörnekler, dll dosyasını oluşturun ve oluşturmak Hierarchyıd_operations Kurul SQL Serveraşağıdakine benzer bir kod yürütme tarafından:
CREATE ASSEMBLY HierarchyId_Operations
FROM '<path to DLL>\ListAncestors.dll'
GO
[YUKARI]
Öncüleri listeleme
Atalarının düğüm listesi oluşturma ortak bir operasyon, örneğin olan kuruluşun bir konumda göstermek için. Yapmanın bir yolu kullanarak bir tablo değerli işlev kullanarak budur Hierarchyıd_operations yukarıda tanımlanan sınıf:
Using Transact-SQL:
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO
Örnek kullanım:
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
JOIN ListAncestors(@h) AS A
ON ED.OrgNode = A.Node
GO
[YUKARI]
En düşük ortak öncül bulmak
Kullanarak Hierarchyıd_operations yukarıda tanımlanan sınıf, aşağıdakileri oluşturmak Transact-SQLişlevi bir hiyerarşi içinde iki düğüm içeren en düşük ortak öncül bulmak için:
CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO
Örnek kullanım:
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ç düğüm/1/1. /
[YUKARI]
Alt ağaçlar taşıma
Başka bir ortak operasyon ağaçların hareket ediyor. Alt ağacı aşağıdaki yordamı alır @ oldMgr ve yapar (dahil @ oldMgr) bir alt ağacı @ newMgr.
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
[YUKARI]