Dados hierárquicos (SQL Server)Hierarchical Data (SQL Server)

O tipo de dados interno hierarchyid facilita o armazenamento e a consulta de dados hierárquicos.The built-in hierarchyid data type makes it easier to store and query hierarchical data. hierarchyid foi otimizado para representar árvores, que são o tipo mais comum de dados hierárquicos.hierarchyid is optimized for representing trees, which are the most common type of hierarchical data.

Os dados hierárquicos são definidos como um conjunto de itens de dados mutuamente relacionados por relações hierárquicas.Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. As relações hierárquicas existem onde um item de dados é o pai de outro item.Hierarchical relationships exist where one item of data is the parent of another item. Exemplos dos dados hierárquicos que geralmente são armazenados em bancos de dados incluem o seguinte:Examples of the hierarchical data that is commonly stored in databases include the following:

  • Uma estrutura organizacionalAn organizational structure

  • Um sistema de arquivosA file system

  • Um conjunto de tarefas em um projetoA set of tasks in a project

  • Uma taxonomia de termos de linguagemA taxonomy of language terms

  • Um gráfico de links entre páginas da WebA graph of links between Web pages

    Use hierarchyid como o tipo de dados para criar tabelas com uma estrutura hierárquica ou para descrever a estrutura hierárquica dos dados armazenados em outro local.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. Use as funções hierarchyid no Transact-SQLTransact-SQL para consultar e gerenciar dados hierárquicos.Use the hierarchyid functions in Transact-SQLTransact-SQL to query and manage hierarchical data.

Propriedades chave de hierarchyid Key Properties of hierarchyid

Um valor do tipo de dados hierarchyid representa uma posição em uma hierarquia de árvore.A value of the hierarchyid data type represents a position in a tree hierarchy. Os valores para hierarchyid têm as seguintes propriedades:Values for hierarchyid have the following properties:

  • Extremamente compactoExtremely compact

    O número médio de bits necessários para representar um nó em uma árvore com n nós depende da média de fanout (o número médio de filhos de um nó).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). Para fanouts pequenos, o tamanho (0-7) é de aproximadamente 6*logAn bits, em que A é o fanout médio.For small fanouts, (0-7) the size is about 6*logAn bits, where A is the average fanout. Um nó em uma hierarquia organizacional de 100.000 pessoas com um fanout médio de 6 níveis usa cerca de 38 bits.A node in an organizational hierarchy of 100,000 people with an average fanout of 6 levels takes about 38 bits. Isso é arredondado para 40 bits, ou 5 bytes, para armazenamento.This is rounded up to 40 bits, or 5 bytes, for storage.

  • A comparação está na ordem de profundidadeComparison is in depth-first order

    Dados dois valores de hierarchyid a r b, a<b significa que a vem antes de b em uma passagem de profundidade da árvore.Given two hierarchyid values a and b, a<b means a comes before b in a depth-first traversal of the tree. Índices em tipos de dados hierarchyid estão na ordem de profundidade e os nós próximos uns dos outros em uma passagem de profundidade são armazenados próximos um ao outro.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. Por exemplo, os filhos de um registro são armazenados adjacentes àquele registro.For example, the children of a record are stored adjacent to that record.

  • Suporte a inserções e exclusões arbitráriasSupport for arbitrary insertions and deletions

    Usando o método GetDescendant , é sempre possível gerar um irmão à direita de qualquer nó determinado, à esquerda de qualquer nó determinado ou entre dois irmãos.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. A propriedade de comparação é mantida quando um número arbitrário de nós é inserido ou excluído da hierarquia.The comparison property is maintained when an arbitrary number of nodes is inserted or deleted from the hierarchy. A maioria das inserções e exclusões preserva a propriedade de densidade.Most insertions and deletions preserve the compactness property. Porém, inserções entre dois nós produzirão valores hierarchyid com uma representação ligeiramente menos compacta.However, insertions between two nodes will produce hierarchyid values with a slightly less compact representation.

Limitações de hierarchyid Limitations of hierarchyid

O tipo de dados hierarchyid tem as seguintes limitações:The hierarchyid data type has the following limitations:

  • Uma coluna de tipo hierarchyid não representa automaticamente uma árvore.A column of type hierarchyid does not automatically represent a tree. Depende do aplicativo gerar e atribuir valores hierarchyid de maneira que a relação desejada entre as linhas seja refletida nos valores.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. Alguns aplicativos podem ter uma coluna do tipo hierarchyid que indica o local em uma hierarquia definida em outra tabela.Some applications might have a column of type hierarchyid that indicates the location in a hierarchy defined in another table.

  • Depende de o aplicativo gerenciar a simultaneidade na geração e atribuição de valores hierarchyid .It is up to the application to manage concurrency in generating and assigning hierarchyid values. Não há nenhuma garantia de que os valores hierarchyid em uma coluna sejam exclusivos a menos que o aplicativo use uma restrição de chave exclusiva ou force sua exclusividade em sua própria lógica.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.

  • Relações hierárquicas representadas por valores hierarchyid não são impostas como uma relação de chave estrangeira.Hierarchical relationships represented by hierarchyid values are not enforced like a foreign key relationship. É possível e, às vezes, apropriado ter uma relação hierárquica onde A tem um filho B e, depois, A é excluído deixando B com uma relação para um registro inexistente.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 esse comportamento for inaceitável, o aplicativo deverá fazer a consulta por descendentes antes de excluir os pais.If this behavior is unacceptable, the application must query for descendants before deleting parents.

Quando usar alternativas para hierarchyid When to Use Alternatives to hierarchyid

As duas alternativas para hierarchyid para representar dados hierárquicos são:Two alternatives to hierarchyid for representing hierarchical data are:

  • Pai/filhoParent/Child

  • XMLXML

    Ahierarchyid é geralmente superior a essas alternativas.hierarchyid is generally superior to these alternatives. Porém, a seguir há situações específicas detalhadas em que as alternativas são provavelmente superiores.However, there are specific situations detailed below where the alternatives are likely superior.

Pai/filhoParent/Child

Ao usar a abordagem Pai/Filho, cada linha contém uma referência ao pai.When using the Parent/Child approach, each row contains a reference to the parent. A tabela a seguir define uma tabela típica usada para conter as linhas pai e filho em uma relação Pai/Filho: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  

Comparando pai/filho e hierarchyid em operações comunsComparing Parent/Child and hierarchyid for Common Operations

  • Consultas de subárvore são significativamente mais rápidas com hierarchyid.Subtree queries are significantly faster with hierarchyid.

  • Consultas de descendente direto são ligeiramente mais lentas com hierarchyid.Direct descendant queries are slightly slower with hierarchyid.

  • A movimentação de nós não folha é mais lenta com hierarchyid.Moving non-leaf nodes is slower with hierarchyid.

  • A inserção de nós não folha e a inserção ou a movimentação de nós folha têm a mesma complexidade com hierarchyid.Inserting non-leaf nodes and inserting or moving leaf nodes has the same complexity with hierarchyid.

    Pai/Filho pode ser superior quando existem as seguintes:Parent/Child might be superior when the following conditions exist:

  • O tamanho da chave é crítico.The size of the key is critical. Para o mesmo número de nós, um valor hierarchyid é igual ou maior que um valor da família de inteiros (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. Essa é a única razão para usar Pai/Filho em casos raros, porque hierarchyid tem localidade significativamente melhor de E/S e complexidade de CPU que as expressões de tabela comuns exigidas quando você está usando uma estrutura Pai/Filho.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.

  • Consultas raramente examinam por seções da hierarquia.Queries rarely query across sections of the hierarchy. Em outras palavras, as consultas normalmente se dirigem apenas a um único ponto na hierarquia.In other words, queries usually address only a single point in the hierarchy. Nesses casos, a colocação não é importante.In these cases co-location is not important. Por exemplo, Pai/Filho é superior quando a tabela de organização é usada somente para processar a folha de pagamento de funcionários individuais.For example, Parent/Child is superior when the organization table is only used to process payroll for individual employees.

  • Subárvores de não folha mudam frequentemente e o desempenho é muito importante.Non-leaf subtrees move frequently and performance is very important. Em uma representação pai/filho, alterando o local de uma linha em uma hierarquia afeta uma linha única.In a parent/child representation changing the location of a row in a hierarchy affects a single row. Alterar o local de uma linha em um uso de hierarchyid afeta n linhas, em que n é número de nós na subárvore sendo movida.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 as subárvores sem folha mudarem frequentemente e o desempenho for importante, mas a maioria das mudanças estiver em um nível bem definido da hierarquia, considere dividir os níveis superiores e inferiores em duas hierarquias.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. Isso faz todas as mudanças em níveis de folha da hierarquia mais alta.This makes all moves into leaf-levels of the higher hierarchy. Por exemplo, considere uma hierarquia de sites hospedados por um serviço.For instance, consider a hierarchy of Web sites hosted by a service. Sites contêm muitas páginas organizadas de uma maneira hierárquica.Sites contain many pages arranged in a hierarchical manner. Sites hospedados poderiam ser movidos a outros locais na hierarquia do site, mas as páginas subordinadas raramente seriam reorganizadas.Hosted sites might be moved to other locations in the site hierarchy, but the subordinate pages are rarely re-arranged. Isso poderia ser representado por:This could be represented via:

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

XMLXML

Um documento XML é uma árvore e, portanto, uma instância de tipo de dados XML única pode representar uma hierarquia completa.An XML document is a tree, and therefore a single XML data type instance can represent a complete hierarchy. No SQL ServerSQL Server quando um índice XML é criado, valores hierarchyid são usados internamente para representar a posição na hierarquia.In SQL ServerSQL Server when an XML index is created, hierarchyid values are used internally to represent the position in the hierarchy.

Usar um tipo de dados XML pode ser vantajoso quando todos os seguintes itens forem verdadeiros:Using XML data type can be superior when all the following are true:

  • A hierarquia completa é sempre armazenada e recuperada.The complete hierarchy is always stored and retrieved.

  • Os dados são consumidos no formato XML pelo aplicativo.The data is consumed in XML format by the application.

  • Pesquisas de predicado são extremamente limitadas e não têm de desempenho crítico.Predicate searches are extremely limited and not performance critical.

    Por exemplo, se um aplicativo controla várias organizações, ele sempre armazena e recupera a hierarquia organizacional completa, e não faz a consulta em uma organização única, uma tabela do formulário a seguir faria sentido: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  

Estratégias de indexação para dados hierárquicos Indexing Strategies for Hierarchical Data

Há duas estratégias para indexar dados hierárquicos:There are two strategies for indexing hierarchical data:

  • ProfundidadeDepth-first

    Um índice de profundidade armazena as linhas em uma subárvore próximas umas das outras.A depth-first index stores the rows in a subtree near each other. Por exemplo, todos os funcionários que se reportam a gerente são armazenados próximos do registro de seus gerentes.For example, all employees that report through a manager are stored near their managers' record.

    Em um índice por profundidade, todos os nós na subárvore de um nó são colocados.In a depth-first index, all nodes in the subtree of a node are co-located. Índices por profundidade são portanto eficientes para responder consultas sobre subárvores, como "Localizar todos os arquivos nesta pasta e subpastas""""""""".Depth-first indexes are therefore efficient for answering queries about subtrees, such as "Find all files in this folder and its subfolders".

  • AmplitudeBreadth-first

    Uma amplitude armazena as linhas de cada nível da hierarquia juntas.A breadth-first stores the rows each level of the hierarchy together. Por exemplo, os registros de funcionários que se reportam diretamente ao mesmo gerente são armazenados próximos um do outro.For example, the records of employees who directly report to the same manager are stored near each other.

    Em um índice por amplitude todos os filhos diretos de um nós são colocados.In a breadth-first index all direct children of a node are co-located. Índices por amplitude são, portanto, eficientes para responder consultas sobre filhos diretos, como "Localizar todos os empregados que se reportam diretamente a esse gerente".Breadth-first indexes are therefore efficient for answering queries about immediate children, such as "Find all employees who report directly to this manager".

    Ter opções por profundidade, por amplitude, ou ambas, e qual delas tornar a chave de clustering (se houver), depende da importância relativa dos tipos de consultas anteriores e da importância relativa de operações SELECT versus 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. Para obter um exemplo detalhado de estratégias de indexação, consulte Tutorial: Usando o tipo de dados HierarchyId.For a detailed example of indexing strategies, see Tutorial: Using the hierarchyid Data Type.

Criando índicesCreating Indexes

O método GetLevel() pode ser usado para criar uma ordem por amplitude.The GetLevel() method can be used to create a breadth first ordering. No exemplo seguinte, são criados índices por amplitude e por profundidade: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  

ExemplosExamples

Exemplo simplesSimple Example

O exemplo a seguir é intencionalmente simplificado para ajudá-lo a começar.The following example is intentionally simplistic to help you get started. Primeiro crie uma tabela para manter alguns dados geográficos.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);  

Agora insira dados para alguns continentes, países, estados e cidades.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');  

Selecione os dados, adicionando uma coluna que converta os dados de nível em um valor de texto de fácil compreensão.Select the data, adding a column that converts the Level data into a text value that is easy to understand. Essa consulta também ordena o resultado pelo tipo de dados 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;  

Aqui está o conjunto de resultados.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  

Observe que a hierarquia tem uma estrutura válida, embora ela não seja consistente internamente.Notice that the hierarchy is has a valid structure, even though it is not internally consistent. Bahia é o único estado.Bahia is the only state. Ele aparece na hierarquia como um par da cidade de Brasília.It appears in the hierarchy as a peer of the city Brasilia. Da mesma forma, a estação McMurdo não tem um país pai.Similarly, McMurdo Station does not have a parent country. Os usuários devem decidir se este tipo de hierarquia é apropriado para seu uso.Users must decide if this type of hierarchy is appropriate for their use.

Adicione outra linha e selecione os resultados.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;  

Isso demonstra mais problemas em potencial.This demonstrates more possible problems. Kyoto pode ser inserido como o nível /1/3/1/ , embora não exista um nível pai /1/3/.Kyoto can be inserted as level /1/3/1/ even though there is no parent level /1/3/. Londres e Kyoto têm o mesmo valor para hierarchyid.And both London and Kyoto have the same value for the hierarchyid. Além disso, os usuários devem decidir se este tipo de hierarquia é apropriado para seu uso, e os valores do bloco que são inválidos para seu uso.Again, users must decide if this type of hierarchy is appropriate for their use, and block values that are invalid for their usage.

Além disso, essa tabela não usou a parte superior da hierarquia '/'.Also, this table did not use the top of the hierarchy '/'. Ela foi omitida pois não há um pai comum de todos os continentes.It was omitted because there is no common parent of all the continents. Para adicionar um, adicione o planeta inteiro.You can add one by adding the whole planet.

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

Migrando de Pai/Filho para hierarchyid Migrating from Parent/Child to hierarchyid

A maioria das árvores é representada usando Pai/Filho.Most trees are represented using Parent/Child. O modo mais fácil de migrar de uma estrutura Pai/Filho para uma tabela usando hierarchyid é usar uma coluna ou uma tabela temporária para manter o controle do número de nós em cada nível da hierarquia.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. Para obter um exemplo de migração de uma tabela Pai/Filho, consulte a lição 1 do Tutorial: Usando o tipo de dados HierarchyId.For an example of migrating a Parent/Child table, see lesson 1 of Tutorial: Using the hierarchyid Data Type.

Gerenciando uma árvore com hierarchyid Managing a Tree Using hierarchyid

Embora uma coluna hierarchyid não represente necessariamente uma árvore, um aplicativo pode garantir facilmente que essa representação ocorra.Although a hierarchyid column does not necessarily represent a tree, an application can easily ensure that it does.

  • Para gerar novos valores, execute uma das ações abaixo:When generating new values, do one of the following:

    • Mantenha registro do último número filho da linha pai.Keep track of the last child number in the parent row.

    • Compute o último filho.Compute the last child. Para executar esse procedimento com eficácia, é necessário um índice de primeira amplitude.Doing this efficiently requires a breadth-first index.

  • Imponha a exclusividade criando um índice exclusivo na coluna, talvez como parte de uma chave de clustering.Enforce uniqueness by creating a unique index on the column, perhaps as part of a clustering key. Para assegurar a inserção de valores únicos, execute uma das ações a seguir:To ensure that unique values are inserted, do one of the following:

    • Detecte as falhas de violação de chave exclusiva e tente novamente.Detect unique key violation failures and retry.

    • Determine a exclusividade de cada novo nó filho e insira-o como parte de uma transação serializável.Determine the uniqueness of each new child node, and insert it as part of a serializable transaction.

Exemplo utilizando detecção de erroExample Using Error Detection

No exemplo a seguir, o código de exemplo computa o novo valor filho de EmployeeId detectando depois quaisquer violações de chave para retorná-las ao marcador INS_EMP para computar novamente o valor de EmployeeId na nova linha: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  

Exemplo utilizando uma transação serializávelExample Using a Serializable Transaction

O tipo de dados Org_BreadthFirst assegura que a determinação de @last_child use uma busca de intervalo.The Org_BreadthFirst index ensures that determining @last_child uses a range seek. Além de outros casos de erro que um aplicativo tente verificar, uma violação da chave duplicada depois da inserção indica uma tentativa de adicionar vários funcionários com a mesma ID e, portanto, @last_child deve ser novamente computado.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. O código a seguir usa uma transação serializável e um índice de primeira amplitude para computar o valor do novo nó: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 ;  

O código a seguir popula a tabela com três linhas e retorna os resultados: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  

Aqui está o conjunto de resultados.Here is the result set.

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

Aplicando uma árvore Enforcing a tree

Os exemplos anteriores ilustram como um aplicativo pode assegurar a manutenção de uma árvore.The above examples illustrate how an application can ensure that a tree is maintained. Para impor uma árvore usando restrições, uma coluna computada que define o pai de cada nó pode ser criada com uma restrição de chave estrangeira na ID de chave primária.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  

O método de impor uma relação é preferido quando o código que não é confiável para manter a árvore hierárquica tiver acesso DML direto à tabela.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. No entanto, esse método pode reduzir o desempenho porque a restrição deve ser verificada em todas as operações DML.However this method might reduce performance because the constraint must be checked on every DML operation.

Localizando ancestrais usando o CLR Finding Ancestors by Using the CLR

Uma operação comum que envolve dois nós em uma hierarquia é encontrar o mais baixo ancestral comum.A common operation involving two nodes in a hierarchy is to find the lowest common ancestor. Isso pode ser escrito em Transact-SQLTransact-SQL ou CLR, porque o tipo hierarchyid está disponível em ambos.This can be written in either Transact-SQLTransact-SQL or CLR, because the hierarchyid type is available in both. CLR é recomendado porque o desempenho será mais rápido.CLR is recommended because performance will be faster.

Use o código CLR a seguir para listar os ancestrais e localizar o ancestral comum mais baixo: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;  
    }  
}  

Para usar os métodos ListAncestor e CommonAncestor nos exemplos Transact-SQLTransact-SQL a seguir, construa a DLL e crie o assembly HierarchyId_Operations em SQL ServerSQL Server executando um código semelhante ao seguinte: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  

Listando os ancestrais Listing Ancestors

A criação de uma lista de ancestrais de um nó é uma operação comum; por exemplo, para mostrar a posição em uma organização.Creating a list of ancestors of a node is a common operation, for instance to show position in an organization. Uma das formas de fazer isso é usar uma função com valor de tabela usando a classe HierarchyId_Operations definida acima:One way of doing this is by using a table-valued-function using the HierarchyId_Operations class defined above:

Usando 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  

Exemplo de uso: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  

Localizando o mais baixo ancestral comum Finding the Lowest Common Ancestor

Usando a classe HierarchyId_Operations definida acima, crie a seguinte função Transact-SQLTransact-SQL para localizar o mais baixo ancestral comum que envolva dois nós em uma hierarquia: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  

Exemplo de uso: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) ;  

O nó resultante é /1/1/The resultant node is /1/1/

Movendo subárvores Moving Subtrees

Outra operação comum é mover subárvores.Another common operation is moving subtrees. O procedimento abaixo toma uma subárvore de @oldMgr e a transforma em ( @oldMgrinclusive) uma subárvore de @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  

Consulte tambémSee Also

Referência de método de tipo de dados hierarchyid hierarchyid Data Type Method Reference
Tutorial: Usando o tipo de dados HierarchyId Tutorial: Using the hierarchyid Data Type
hierarchyid (Transact-SQL) hierarchyid (Transact-SQL)