Dati gerarchici [SQL Server]Hierarchical Data (SQL Server)

Con il tipo di dati hierarchyid predefinito, l'archiviazione e l'esecuzione di query sui dati gerarchici risultano più semplici.The built-in hierarchyid data type makes it easier to store and query hierarchical data. hierarchyid è ottimizzato per la rappresentazione di alberi, ovvero il tipo più comune di dati gerarchici.hierarchyid is optimized for representing trees, which are the most common type of hierarchical data.

I dati gerarchici vengono definiti come un set di elementi di dati correlati tra loro tramite relazioni gerarchiche.Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Si parla di relazioni gerarchiche quando un elemento di dati è l'elemento padre di un altro elemento.Hierarchical relationships exist where one item of data is the parent of another item. Di seguito sono riportati alcuni esempi dei dati gerarchici comunemente archiviati nei database:Examples of the hierarchical data that is commonly stored in databases include the following:

  • Una struttura organizzativaAn organizational structure

  • Un file systemA file system

  • Un set di attività di un progettoA set of tasks in a project

  • Una tassonomia di termini del linguaggioA taxonomy of language terms

  • Un grafico di collegamenti tra pagine WebA graph of links between Web pages

    Usare hierarchyid come tipo di dati per creare tabelle con una struttura gerarchica o per descrivere la struttura gerarchica dei dati archiviati in un altro percorso.Use hierarchyid as a data type to create tables with a hierarchical structure, or to describe the hierarchical structure of data that is stored in another location. Usare le funzioni hierarchyid di Transact-SQLTransact-SQL per eseguire query e gestire i dati gerarchici.Use the hierarchyid functions in Transact-SQLTransact-SQL to query and manage hierarchical data.

Proprietà chiave di hierarchyid Key Properties of hierarchyid

Un valore del tipo di dati hierarchyid rappresenta una posizione in un albero gerarchico.A value of the hierarchyid data type represents a position in a tree hierarchy. I valori per hierarchyid hanno le proprietà seguenti:Values for hierarchyid have the following properties:

  • Estremamente compressoExtremely compact

    Il numero medio di bit richiesto per rappresentare un nodo in un albero con n nodi dipende dal fanout medio, ovvero il numero medio di elementi figlio di un nodo.The average number of bits that are required to represent a node in a tree with n nodes depends on the average fanout (the average number of children of a node). Per i fanout piccoli (0-7), la dimensione è approssimativamente 6*logAn bit, dove A è il fanout medio.For small fanouts, (0-7) the size is about 6*logAn bits, where A is the average fanout. Un nodo in una gerarchia organizzativa di 100.000 persone con un fanout medio di 6 livelli richiede circa 38 bit.A node in an organizational hierarchy of 100,000 people with an average fanout of 6 levels takes about 38 bits. Viene arrotondato a 40 bit, o 5 byte, per l'archiviazione.This is rounded up to 40 bits, or 5 bytes, for storage.

  • Il confronto avviene in ordine di scorrimento in profonditàComparison is in depth-first order

    Dati due valori hierarchyid a e b, a<b indica che a precede b nell'attraversamento del primo livello di profondità dell'albero.Given two hierarchyid values a and b, a<b means a comes before b in a depth-first traversal of the tree. Gli indici sui tipi di dati hierarchyid sono in ordine di scorrimento della profondità e i nodi l'uno vicino all'altro nell'attraversamento del primo livello di profondità della struttura sono archiviati l'uno vicino all'altro.Indexes on hierarchyid data types are in depth-first order, and nodes close to each other in a depth-first traversal are stored near each other. Ad esempio, i figli di un record sono archiviati adiacenti al record specifico.For example, the children of a record are stored adjacent to that record.

  • Supporto per eliminazioni e inserimenti arbitrariSupport for arbitrary insertions and deletions

    Usando il metodo GetDescendant , è sempre possibile generare un elemento di pari livello a destra o a sinistra di un nodo oppure tra due elementi di pari livello.By using the GetDescendant method, it is always possible to generate a sibling to the right of any given node, to the left of any given node, or between any two siblings. La proprietà del confronto è gestita quando un numero arbitrario di nodi viene inserito o eliminato dalla gerarchia.The comparison property is maintained when an arbitrary number of nodes is inserted or deleted from the hierarchy. La maggior parte degli inserimenti e delle eliminazioni mantiene la proprietà di compattezza.Most insertions and deletions preserve the compactness property. Tuttavia, gli inserimenti tra due nodi produrranno i valori hierarchyid con una rappresentazione leggermente meno compatta.However, insertions between two nodes will produce hierarchyid values with a slightly less compact representation.

Limitazioni di hierarchyid Limitations of hierarchyid

Al tipo di dati hierarchyid vengono applicate le limitazioni seguenti:The hierarchyid data type has the following limitations:

  • Una colonna di tipo hierarchyid non rappresenta automaticamente un albero.A column of type hierarchyid does not automatically represent a tree. È compito dell'applicazione generare e assegnare i valori hierarchyid in maniera tale che la relazione desiderata tra le righe sia riflessa nei valori.It is up to the application to generate and assign hierarchyid values in such a way that the desired relationship between rows is reflected in the values. In alcune applicazioni potrebbe essere presente una colonna di tipo hierarchyid in cui viene indicato il percorso in una gerarchia definita in un'altra tabella.Some applications might have a column of type hierarchyid that indicates the location in a hierarchy defined in another table.

  • La concorrenza nella generazione e nell'assegnazione dei valori hierarchyid deve essere gestita dall'applicazione.It is up to the application to manage concurrency in generating and assigning hierarchyid values. Non c'è garanzia che i valori hierarchyid di una colonna siano univoci a meno che l'applicazione usi un vincolo della chiave univoca o applichi univocità stessa tramite la logica.There is no guarantee that hierarchyid values in a column are unique unless the application uses a unique key constraint or enforces uniqueness itself through its own logic.

  • Le relazioni gerarchiche rappresentate dai valori hierarchyid non sono applicate come una relazione della chiave esterna.Hierarchical relationships represented by hierarchyid values are not enforced like a foreign key relationship. È possibile e qualche volta appropriato avere una relazione gerarchica dove A ha un figlio B, A viene eliminato e lascia a B una relazione con un record inesistente.It is possible and sometimes appropriate to have a hierarchical relationship where A has a child B, and then A is deleted leaving B with a relationship to a nonexistent record. Se questo comportamento è inaccettabile, tramite l'applicazione deve essere eseguita una query per i discendenti prima di eliminare gli elementi padre.If this behavior is unacceptable, the application must query for descendants before deleting parents.

Quando utilizzare le alternative a hierarchyid When to Use Alternatives to hierarchyid

Di seguito sono elencate due alternative a hierarchyid per la rappresentazione di dati gerarchici:Two alternatives to hierarchyid for representing hierarchical data are:

  • Elemento padre/figlioParent/Child

  • XMLXML

    hierarchyid è generalmente superiore a queste alternative.hierarchyid is generally superior to these alternatives. Tuttavia, esistono determinate situazioni illustrate in dettaglio di seguito in cui le alternative sono superiori.However, there are specific situations detailed below where the alternatives are likely superior.

Elemento padre/figlioParent/Child

Quando si utilizza l'approccio dell'elemento padre/figlio, ogni riga contiene un riferimento all'elemento padre.When using the Parent/Child approach, each row contains a reference to the parent. La tabella seguente definisce una tabella tipica utilizzata per contenere le righe padre e figlio in una relazione padre/figlio:The following table defines a typical table used to contain the parent and the child rows in a Parent/Child relationship:

USE AdventureWorks2012 ;  
GO  

CREATE TABLE ParentChildOrg  
   (  
    BusinessEntityID int PRIMARY KEY,  
    ManagerId int REFERENCES ParentChildOrg(BusinessEntityID),  
    EmployeeName nvarchar(50)   
   ) ;  
GO  

Confronto padre/figlio e hierarchyid per operazioni comuniComparing Parent/Child and hierarchyid for Common Operations

  • Le query del sottoalbero sono notevolmente più veloci con hierarchyid.Subtree queries are significantly faster with hierarchyid.

  • Le query discendenti dirette sono leggermente più lente con hierarchyid.Direct descendant queries are slightly slower with hierarchyid.

  • Lo spostamento di nodi non foglia è più lento con hierarchyid.Moving non-leaf nodes is slower with hierarchyid.

  • L'inserimento di nodi non foglia e l'inserimento o lo spostamento di nodi foglia sono caratterizzati dalla stessa complessità con hierarchyid.Inserting non-leaf nodes and inserting or moving leaf nodes has the same complexity with hierarchyid.

    La relazione elemento padre/figlio potrebbe essere superiore quando esistono le condizioni seguenti:Parent/Child might be superior when the following conditions exist:

  • La dimensione della chiave è importante.The size of the key is critical. Per lo stesso numero di nodi, un valore hierarchyid è uguale a o maggiore di un valore della famiglia Integer (smallint, int, bigint).For the same number of nodes, a hierarchyid value is equal to or larger than an integer-family (smallint, int, bigint) value. Questo è solo uno dei motivi per cui usare la relazione padre/figlio in casi rari, poiché hierarchyid si colloca meglio nell'I/O e nella complessità della CPU che nelle espressioni della tabella comune richieste quando si usa la struttura padre/figlio.This is only a reason to use Parent/Child in rare cases, because hierarchyid has significantly better locality of I/O and CPU complexity than the common table expressions required when you are using a Parent/Child structure.

  • Le query vengono eseguite raramente sulle sezioni della gerarchia.Queries rarely query across sections of the hierarchy. In altre parole, le query di solito vengono eseguite solo su un singolo punto della gerarchia.In other words, queries usually address only a single point in the hierarchy. In questi casi la condivisione percorso non è importante.In these cases co-location is not important. Ad esempio, la relazione elemento padre/figlio è superiore se la tabella dell'organizzazione viene utilizzata solo per l'elaborazione del libro paga per i singoli dipendenti.For example, Parent/Child is superior when the organization table is only used to process payroll for individual employees.

  • I sottoalberi non-foglia vengono spostati frequentemente e le prestazioni sono molto importanti.Non-leaf subtrees move frequently and performance is very important. In una rappresentazione padre/figlio, la modifica del percorso di una riga in una gerarchia influisce su una sola riga.In a parent/child representation changing the location of a row in a hierarchy affects a single row. La modifica del percorso di una riga in un uso di hierarchyid influisce su n righe, dove n è il numero di nodi del sottoalbero spostato.Changing the location of a row in a hierarchyid usage affects n rows, where n is number of nodes in the sub-tree being moved.

    Se i sottoalberi non-foglia vengono spostati frequentemente e le prestazioni sono importanti, ma la maggior parte degli spostamenti è a un livello ben definito della gerarchia, suddividere i livelli superiori e inferiori in due gerarchie.If the non-leaf subtrees move frequently and performance is important, but most of the moves are at a well-defined level of the hierarchy, consider splitting the higher and lower levels into two hierarchies. In questo modo, tutti gli spostamenti si verificano a livello di foglia nella gerarchia più elevata.This makes all moves into leaf-levels of the higher hierarchy. Ad esempio, considerare una gerarchia di siti Web ospitata da un servizio.For instance, consider a hierarchy of Web sites hosted by a service. I siti contengono molte pagine disposte in modo gerarchico.Sites contain many pages arranged in a hierarchical manner. I siti ospitati potrebbero essere spostati in altri percorsi nella gerarchia del sito, tuttavia le pagine subordinate vengono raramente disposte in modo nuovo.Hosted sites might be moved to other locations in the site hierarchy, but the subordinate pages are rarely re-arranged. Questo potrebbe essere rappresentato tramite:This could be represented via:

    CREATE TABLE HostedSites   
       (  
        SiteId hierarchyid, PageId hierarchyid  
       ) ;  
    GO  
    

XMLXML

Un documento XML è un albero e pertanto una singola istanza del tipo di dati XML può rappresentare una gerarchia completa.An XML document is a tree, and therefore a single XML data type instance can represent a complete hierarchy. In SQL ServerSQL Server quando viene creato un indice XML, i valori hierarchyid vengono usati internamente per rappresentare la posizione nella gerarchia.In SQL ServerSQL Server when an XML index is created, hierarchyid values are used internally to represent the position in the hierarchy.

L'utilizzo del tipo di dati XML può essere superiore quando tutte le seguenti condizioni sono vere:Using XML data type can be superior when all the following are true:

  • La gerarchia completa viene sempre archiviata e recuperata.The complete hierarchy is always stored and retrieved.

  • I dati vengono utilizzati in un formato XML dall'applicazione.The data is consumed in XML format by the application.

  • Le ricerche del predicato sono estremamente limitate e non sono importanti per la prestazione.Predicate searches are extremely limited and not performance critical.

    Ad esempio, se tramite un'applicazione vengono rilevate più organizzazioni, viene sempre archiviata e recuperata la gerarchia organizzativa completa e non viene eseguita alcuna query su una singola organizzazione, potrebbe essere utile una tabella con il formato seguente:For example, if an application tracks multiple organizations, always stores and retrieves the complete organizational hierarchy, and does not query into a single organization, a table of the following form might make sense:

CREATE TABLE XMLOrg   
    (  
    Orgid int,  
    Orgdata xml  
    ) ;  
GO  

Strategie di indicizzazione per i dati gerarchici Indexing Strategies for Hierarchical Data

Sono disponibili due strategie per indicizzare i dati gerarchici:There are two strategies for indexing hierarchical data:

  • Depth-firstDepth-first

    In un indice depth-first le righe di un sottoalbero vengono archiviate le une accanto alle altre.A depth-first index stores the rows in a subtree near each other. Ad esempio, tutti i dipendenti che riportano a un responsabile vengono archiviati accanto al record dei responsabili.For example, all employees that report through a manager are stored near their managers' record.

    In un indice depth-first tutti i nodi del sottoalbero di un nodo vengono posizionati insieme.In a depth-first index, all nodes in the subtree of a node are co-located. Gli indici depth-first sono pertanto utili per rispondere alle query sui sottoalberi, ad esempio "Trova tutti i file in questa cartella e nelle relative sottocartelle".Depth-first indexes are therefore efficient for answering queries about subtrees, such as "Find all files in this folder and its subfolders".

  • Breadth-firstBreadth-first

    In un indice breadth-first le righe di ogni livello della gerarchia vengono archiviate insieme.A breadth-first stores the rows each level of the hierarchy together. Ad esempio, i record dei dipendenti che riportano direttamente allo stesso responsabile vengono archiviati gli uni accanto agli altri.For example, the records of employees who directly report to the same manager are stored near each other.

    In un indice breadth-first tutti gli elementi figlio diretti di un nodo vengono posizionati insieme.In a breadth-first index all direct children of a node are co-located. Pertanto, gli indici breadth-first sono in grado di fornire risposte alle query sugli elementi figlio immediati, ad esempio "Trova tutti i dipendenti che riportano direttamente a questo responsabile".Breadth-first indexes are therefore efficient for answering queries about immediate children, such as "Find all employees who report directly to this manager".

    La scelta tra depth-first, breadth-first o entrambi e la selezione di uno di questi come chiave di clustering (se disponibile) dipendono dall'importanza relativa dei tipi di query riportati in precedenza e dall'importanza relativa delle operazioni SELECT e DML.Whether to have depth-first, breadth-first, or both, and which to make the clustering key (if any), depends on the relative importance of the above types of queries, and the relative importance of SELECT vs. DML operations. Per un esempio dettagliato delle strategie di indicizzazione, vedere Esercitazione: Utilizzo del tipo di dati hierarchyid.For a detailed example of indexing strategies, see Tutorial: Using the hierarchyid Data Type.

Creazione di indiciCreating Indexes

Il metodo GetLevel() può essere usato per creare un ordine breadth-first.The GetLevel() method can be used to create a breadth first ordering. Nell'esempio seguente vengono creati sia gli indici breadth-first sia quelli depth-first:In the following example, both breadth-first and depth-first indexes are created:

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  

EsempiExamples

Esempio sempliceSimple Example

L'esempio seguente è intenzionalmente semplicistico per agevolare l'utilizzo iniziale.The following example is intentionally simplistic to help you get started. Creare innanzitutto una tabella per contenere alcuni dati geography.First create a table to hold some geography data.

CREATE TABLE SimpleDemo  
(Level hierarchyid NOT NULL,  
Location nvarchar(30) NOT NULL,  
LocationType nvarchar(9) NULL);  

A questo punto inserire i dati per alcuni continenti, paesi, stati e città.Now insert data for some continents, countries, states, and cities.

INSERT SimpleDemo  
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');  

Selezionare i dati, aggiungendo una colonna in cui i dati del livello vengono convertiti in un valore di testo facile da capire.Select the data, adding a column that converts the Level data into a text value that is easy to understand. Tramite questa query è inoltre possibile ordinare il risultato in base al tipo di dati hierarchyid .This query also orders the result by the hierarchyid data type.

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

Set di risultati:Here is the result set.

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  

Si noti che la gerarchia ha una struttura valida, anche se non è coerente internamente.Notice that the hierarchy has a valid structure, even though it is not internally consistent. Bahia è l'unico stato.Bahia is the only state. Viene visualizzato nella gerarchia come peer della città Brasilia.It appears in the hierarchy as a peer of the city Brasilia. Analogamente, McMurdo Station non dispone di un paese padre.Similarly, McMurdo Station does not have a parent country. Gli utenti dovranno decidere se questo tipo di gerarchia è appropriato per l'utilizzo.Users must decide if this type of hierarchy is appropriate for their use.

Aggiungere un'altra riga e selezionare i risultati.Add another row and select the results.

INSERT SimpleDemo  
VALUES ('/1/3/1/', 'Kyoto', 'City'), ('/1/3/1/', 'London', 'City');  
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;  

In questo modo vengono illustrati altri possibili problemi.This demonstrates more possible problems. Kyoto può essere inserita come livello /1/3/1/ anche se non esiste alcun livello padre /1/3/.Kyoto can be inserted as level /1/3/1/ even though there is no parent level /1/3/. Inoltre, Londra e Kyoto hanno lo stesso valore per hierarchyid.And both London and Kyoto have the same value for the hierarchyid. Anche in questo caso, gli utenti dovranno decidere se questo tipo di gerarchia è appropriato per l'utilizzo e bloccare i valori che non sono validi per lo scopo.Again, users must decide if this type of hierarchy is appropriate for their use, and block values that are invalid for their usage.

Inoltre, in questa tabella non è stato utilizzato il primo livello della gerarchia '/'.Also, this table did not use the top of the hierarchy '/'. È stato omesso perché non vi è alcun elemento padre comune per tutti i continenti.It was omitted because there is no common parent of all the continents. È possibile inserirne uno aggiungendo l'intero pianeta.You can add one by adding the whole planet.

INSERT SimpleDemo  
VALUES ('/', 'Earth', 'Planet');  

Migrazione dalla relazione elemento padre/figlio a hierarchyid Migrating from Parent/Child to hierarchyid

La maggior parte degli alberi viene rappresentata utilizzando la relazione elemento padre/figlio.Most trees are represented using Parent/Child. Il modo più semplice per eseguire la migrazione da una struttura elemento padre/figlio a una tabella tramite hierarchyid consiste nell'usare una colonna o una tabella temporanea per tenere traccia del numero di nodi a ogni livello della gerarchia.The easiest way to migrate from a Parent/Child structure to a table using hierarchyid is to use a temporary column or a temporary table to keep track of the number of nodes at each level of the hierarchy. Per un esempio di migrazione di una tabella padre/figlio, vedere la lezione 1 di Esercitazione: Utilizzo del tipo di dati hierarchyid.For an example of migrating a Parent/Child table, see lesson 1 of Tutorial: Using the hierarchyid Data Type.

Gestione di un albero tramite hierarchyid Managing a Tree Using hierarchyid

Un'applicazione può assicurarsi facilmente che una colonna hierarchyid rappresenti un albero anche se ciò non accade necessariamente.Although a hierarchyid column does not necessarily represent a tree, an application can easily ensure that it does.

  • Durante la generazione di nuovi valori, eseguire una delle operazioni seguenti:When generating new values, do one of the following:

    • Monitorare l'ultimo numero figlio nella riga padre.Keep track of the last child number in the parent row.

    • Calcolare l'ultimo elemento figlio.Compute the last child. Ciò richiede un indice breadth-first.Doing this efficiently requires a breadth-first index.

  • Applicare l'univocità creando un indice univoco sulla colonna, come parte di una chiave di clustering.Enforce uniqueness by creating a unique index on the column, perhaps as part of a clustering key. Per garantire che vengano inseriti valori univoci, effettuare una delle operazioni seguenti:To ensure that unique values are inserted, do one of the following:

    • Rilevare errori di violazione di chiave univoca e riprovare.Detect unique key violation failures and retry.

    • Determinare l'univocità di ogni nuovo nodo figlio e inserirlo come parte di una transazione serializzabile.Determine the uniqueness of each new child node, and insert it as part of a serializable transaction.

Esempio di utilizzo del rilevamento degli erroriExample Using Error Detection

Nell'esempio seguente, il codice consente calcola il nuovo valore EmployeeId figlio, quindi rileva eventuali violazioni di chiave e torna al marcatore INS_EMP per ricalcolare il valore EmployeeId per la nuova riga:In the following example, the sample code computes the new child EmployeeId value, and then detects any key violation and returns to INS_EMP marker to recompute the EmployeeId value for the new row:

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  

Esempio di utilizzo di una transazione serializzabileExample Using a Serializable Transaction

Al tipo di dati Org_BreadthFirst viene assicurato che per la determinazione di @last_child venga usata una ricerca di intervallo.The Org_BreadthFirst index ensures that determining @last_child uses a range seek. Oltre ad altri casi di errore che potrebbero voler essere controllati da un'applicazione, una violazione di chiave duplicata dopo l'inserimento indica un tentativo di aggiunta di più dipendenti con lo stesso ID. Questa situazione richiede pertanto il ricalcolo di @last_child .In addition to other error cases an application might want to check, a duplicate key violation after the insert indicates an attempt to add multiple employees with the same id, and therefore @last_child must be recomputed. Nel codice seguente sono utilizzati una transazione serializzabile e un indice breadth-first per calcolare il valore del nodo nuovo:The following code uses a serializable transaction and a breadth-first index to compute the new node value:

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 ;  

Tramite il codice seguente la tabella viene popolata con tre righe e vengono restituiti i risultati:The following code populates the table with three rows and returns the results:

INSERT Org_T2 (EmployeeId, EmployeeName)   
    VALUES(hierarchyid::GetRoot(), 'David') ;  
GO  
AddEmp 0x , 'Sariya'  
GO  
AddEmp 0x58 , 'Mary'  
GO  
SELECT * FROM Org_T2  

Set di risultati:Here is the result set.

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

Applicazione di un albero Enforcing a tree

Negli esempi riportati in precedenza si illustra la possibilità di gestione di un albero da parte di un'applicazione.The above examples illustrate how an application can ensure that a tree is maintained. Per applicare un albero tramite vincoli, è possibile creare una colonna calcolata in cui viene definito l'elemento padre di ogni nodo con un vincolo di chiave esterna relativo all'ID della chiave primaria.To enforce a tree by using constraints, a computed column that defines the parent of each node can be created with a foreign key constraint back to the primary key id.

CREATE TABLE Org_T3  
(  
   EmployeeId hierarchyid PRIMARY KEY,  
   ParentId AS EmployeeId.GetAncestor(1) PERSISTED    
      REFERENCES Org_T3(EmployeeId),  
   LastChild hierarchyid,   
   EmployeeName nvarchar(50)  
)  
GO  

Questo metodo dell'applicazione di una relazione è preferito quando per il codice considerato non affidabile per la gestione dell'albero gerarchico è disponibile un accesso DML diretto alla tabella.This method of enforcing a relationship is preferred when code that is not trusted to maintain the hierarchical tree has direct DML access to the table. Tuttavia, questo metodo potrebbe ridurre le prestazioni poiché è necessario controllare il vincolo in ogni operazione DML.However this method might reduce performance because the constraint must be checked on every DML operation.

Individuazione di predecessori tramite CLR Finding Ancestors by Using the CLR

Un'operazione comune che interessa due nodi in una gerarchia è la ricerca del predecessore comune minore.A common operation involving two nodes in a hierarchy is to find the lowest common ancestor. Questo può essere scritto in Transact-SQLTransact-SQL o CLR poiché il tipo hierarchyid è disponibile per entrambi.This can be written in either Transact-SQLTransact-SQL or CLR, because the hierarchyid type is available in both. Si consiglia di usare CLR poiché le prestazioni sono più rapide.CLR is recommended because performance will be faster.

Utilizzare il codice CLR seguente per elencare i predecessori e cercare il predecessore comune minore:Use the following CLR code to list ancestors and to find the lowest common ancestor:

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;  
    }  
}  

Per usare i metodi ListAncestor e CommonAncestor negli esempi Transact-SQLTransact-SQL seguenti, compilare la DLL e creare l'assembly HierarchyId_Operations in SQL ServerSQL Server eseguendo un codice simile al seguente:To use the ListAncestor and CommonAncestor methods in the following Transact-SQLTransact-SQL examples, build the DLL and create the HierarchyId_Operations assembly in SQL ServerSQL Server by executing code similar to the following:

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

Elenco dei predecessori Listing Ancestors

La creazione di un elenco dei predecessori di un nodo è un'operazione comune che consente, ad esempio, di mostrare le posizioni di un'organizzazione.Creating a list of ancestors of a node is a common operation, for instance to show position in an organization. A questo scopo, è possibile usare una funzione con valori di tabella tramite la classe HierarchyId_Operations definita in precedenza:One way of doing this is by using a table-valued-function using the HierarchyId_Operations class defined above:

Utilizzo di Transact-SQLTransact-SQL:Using Transact-SQLTransact-SQL:

CREATE FUNCTION ListAncestors (@node hierarchyid)  
RETURNS TABLE (node hierarchyid)  
AS  
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors  
GO  

Esempio di utilizzo:Example of usage:

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  

Ricerca del predecessore comune minore Finding the Lowest Common Ancestor

Usando la classe HierarchyId_Operations definita in precedenza, creare la funzione Transact-SQLTransact-SQL seguente per cercare il predecessore comune minore che interessa due nodi di una gerarchia:Using the HierarchyId_Operations class defined above, create the following Transact-SQLTransact-SQL function to find the lowest common ancestor involving two nodes in a hierarchy:

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

Esempio di utilizzo:Example of usage:

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) ;  

Il nodo risultante è /1/1/The resultant node is /1/1/

Spostamento di sottoalberi Moving Subtrees

Un'altra operazione comune è lo spostamento di sottoalberi.Another common operation is moving subtrees. La procedura descritta di seguito prende in considerazione il sottoalbero di @oldMgr e lo trasforma (includendo @oldMgr) in un sottoalbero di @newMgr.The procedure below takes the subtree of @oldMgr and makes it (including @oldMgr) a subtree of @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  

Vedere ancheSee Also

Guida di riferimento ai metodi per il tipo di dati hierarchyid hierarchyid Data Type Method Reference
Esercitazione: Utilizzo del tipo di dati hierarchyid Tutorial: Using the hierarchyid Data Type
hierarchyid (Transact-SQL)hierarchyid (Transact-SQL)