Dados hierárquicos (SQL Server)

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de dados SQL no Microsoft Fabric

O tipo de dados hierarchyid interno facilita o armazenamento e a consulta de dados hierárquicos. HierarchyId é otimizado para representar árvores, que são o tipo mais comum de dados hierárquicos.

Dados hierárquicos são definidos como um conjunto de itens de dados que estão relacionados entre si por relações hierárquicas. Existem relações hierárquicas em que um item de dados é o pai de outro item. Exemplos dos dados hierárquicos normalmente armazenados em bancos de dados incluem os seguintes itens:

Uma estrutura organizacional
Um sistema de arquivos
Um conjunto de tarefas em um projeto
Uma taxonomia de termos linguísticos
Um gráfico de links entre páginas da Web

Use hierarchyid como um tipo de dados para criar tabelas com uma estrutura hierárquica ou para descrever a estrutura hierárquica de dados armazenados em outro local. Use as funções hierarchyid no Transact-SQL para interrogar e gerir dados hierárquicos.

Propriedades chave

Um valor do hierarchyid tipo de dados representa uma posição em uma hierarquia de árvore. Os valores para hierarchyid têm as seguintes propriedades:

Extremamente compacto

O número médio de bits necessários para representar um nó em uma árvore com n nós depende da distribuição média (o número médio de filhos de um nó). Para pequenos fanouts (0-7), o tamanho é de cerca de $6log{A}{n}$ bits, onde A é o fanout médio. Um nó em uma hierarquia organizacional de 100.000 pessoas com uma extensão média a seis níveis requer cerca de 38 bits. Isso é arredondado para 40 bits, ou 5 bytes, para armazenamento.
A comparação é de primeira ordem

Dado dois valores hierarchyida e b, a < b significa que a vem antes de b na travessia em profundidade da árvore. Os índices em tipos de dados hierarchyid estão em profundidade de primeira ordem, e nós próximos uns dos outros em uma travessia de profundidade primeiro são armazenados próximos uns dos outros. Por exemplo, os filhos de um registro são armazenados ao lado desse registro.
Suporte para inserções e exclusões arbitrárias

Usando o método GetDescendant (Mecanismo de Banco de Dados), é sempre possível gerar um irmão à direita de qualquer node, à esquerda de qualquer node ou entre quaisquer dois irmãos. A propriedade de comparação é mantida quando um número arbitrário de nós é inserido ou excluído da hierarquia. A maioria das inserções e exclusões preserva a propriedade de compacidade. No entanto, inserções entre dois nós produzem valores hierarchyid com uma representação um pouco menos compacta.

Limitations

O tipo de dados hierarchyid tem as seguintes limitações:

Uma coluna do tipo hierarchyid não representa automaticamente uma árvore. Cabe ao aplicativo gerar e atribuir valores hierarchyid de tal forma que a relação desejada entre linhas seja refletida nos valores. Alguns aplicativos podem ter uma coluna do tipo hierarchyid que indica o local em uma hierarquia definida em outra tabela.
Cabe ao aplicativo gerenciar a simultaneidade na geração e atribuição de valores hierarchyid . Não há garantia de que os valores hierarchyid em uma coluna sejam exclusivos, a menos que o aplicativo use uma restrição de chave exclusiva ou imponha a própria exclusividade por meio de sua própria lógica.
As relações hierárquicas representadas por valores hierarchyid não são impostas como uma relação de chave estrangeira. É 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. Se esse comportamento for inaceitável, o aplicativo deve consultar descendentes antes de excluir pais.

Quando usar alternativas ao hierarchyid

Duas alternativas ao hierarchyid para representar dados hierárquicos são:

Parent/child
XML

Hierarchyid é geralmente superior a essas alternativas. No entanto, existem situações específicas, detalhadas neste artigo, em que as alternativas são provavelmente superiores.

Parent/child

Quando você usa a abordagem pai/filho, cada linha contém uma referência ao pai. A tabela a seguir define uma tabela típica usada para conter as linhas pai e filho em uma relação pai/filho:

USE AdventureWorks2022;
GO

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

Comparando pai/filho e hierarchyid para operações comuns:

As consultas de subárvore são significativamente mais rápidas com hierarchyid.
As consultas descendentes diretas são um pouco mais lentas com hierarchyid.
A movimentação de nós não foliares é mais lenta com hierarchyid.
Inserir nós não foliares e inserir ou mover nós de folha têm a mesma complexidade com hierarchyid.

Os pais/filhos podem ser superiores quando existem as seguintes condições:

O tamanho da chave é crítico. Para o mesmo número de nós, um valor hierarchyid é igual ou maior do que um valor da família de inteiros (smallint, int, bigint). Isso é apenas um motivo para se usar uma estrutura pai/filho em casos raros, porque hierarchyid tem uma melhor localidade de E/S e menor complexidade de CPU do que as expressões de tabela comuns necessárias ao utilizar uma estrutura pai/filho.
As consultas raramente percorrem diferentes secções da hierarquia. Em outras palavras, as consultas geralmente abordam apenas um único ponto na hierarquia. Nestes casos, a colocação não é importante. Por exemplo, pai/filho é superior quando a tabela organizacional é usada apenas para processar a folha de pagamento de funcionários individuais.
As subárvores não foliares movem-se com frequência e o desempenho é muito importante. Em uma representação pai/filho, alterar o local de uma linha em uma hierarquia afeta uma única linha. Alterar o local de uma linha na hierarquia hierarchyid afeta n linhas, onde n é a numeração de nós na subárvore a mover.

Se as subárvores não foliares se movimentam com frequência e o desempenho é importante, mas a maioria dos movimentos está a um nível bem definido da hierarquia, considere dividir os níveis mais altos e mais baixos em duas estruturas hierárquicas. Isso faz com que todos os movimentos passem para os níveis de folha da hierarquia superior. Por exemplo, considere uma hierarquia de sites hospedados por um serviço. Os sites contêm muitas páginas organizadas de forma hierárquica. Os sites hospedados podem ser movidos para outros locais na hierarquia de sites, mas as páginas subordinadas raramente são reorganizadas. Isto pode ser representado através de:
```
CREATE TABLE HostedSites (
    SiteId HIERARCHYID,
    PageId HIERARCHYID
);
GO
```

XML

Um documento XML é uma árvore e, portanto, uma única instância de tipo de dados XML pode representar uma hierarquia completa. No SQL Server, quando um índice XML é criado, os valores hierarchyid são usados internamente para representar a posição na hierarquia.

O uso do tipo de dados XML pode ser superior quando todos os itens a seguir forem verdadeiros:

A hierarquia completa é sempre armazenada e recuperada.
Os dados são consumidos em formato XML pelo aplicativo.
As pesquisas de predicados são extremamente limitadas e não são críticas para o desempenho.

Por exemplo, se um aplicativo rastreia várias organizações, sempre armazena e recupera a hierarquia organizacional completa e não consulta em uma única organização, uma tabela do seguinte formulário pode fazer sentido:

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

Estratégias de índice para dados hierárquicos

Existem duas estratégias para indexar dados hierárquicos:

Depth-first

Um índice de profundidade primeiro armazena as linhas em uma subárvore próximas umas das outras. Por exemplo, todos os funcionários que reportam a um gerente são armazenados junto ao registo dos seus respetivos gerentes.

Em um índice primeiro a profundidade, todos os nós na subárvore de um nó são agrupados. Os índices de buscas em profundidade são, portanto, eficientes para responder a consultas sobre subárvores, como: "Encontrar todos os ficheiros nesta pasta e nas suas subpastas"
Breadth-first

Um índice de amplitude primeiro armazena as linhas de cada nível da hierarquia juntas. Por exemplo, os registros de funcionários que se reportam diretamente ao mesmo gerente são armazenados próximos uns dos outros.

Em um índice amplitude-primeiro, todos os filhos diretos de um nó são colocalizados. Os índices de amplitude são, portanto, eficientes para responder a perguntas sobre filhos imediatos, tais como: "Encontre todos os funcionários que se reportam diretamente a este gerente"

Se deve ter em profundidade, em largura ou ambos, e qual fazer a chave de agrupamento (se houver), depende da importância relativa dos tipos de consultas acima e da importância relativa das operações SELECT em comparação com as operações DML. Para obter um exemplo detalhado de estratégias de indexação, consulte Tutorial: Usando o tipo de dados hierarchyid.

Criar índices

O método GetLevel() pode ser usado para criar uma primeira ordem de amplitude. No exemplo a seguir, os índices de amplitude primeiro e profundidade são criados:

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

Examples

Os exemplos de código neste artigo usam o banco de dados de exemplo AdventureWorks2025 ou AdventureWorksDW2025, que pode ser descarregado da página inicial de Exemplos e Projetos da Comunidade do Microsoft SQL Server.

Exemplo básico

O exemplo a seguir é intencionalmente simplista para ajudá-lo a começar. Primeiro, crie uma tabela para armazenar alguns dados geográficos.

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

Agora insira dados para alguns continentes, países/regiões, estados e cidades.

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

Selecione os dados, adicionando uma coluna que converte os dados de nível em um valor de texto fácil de entender. Essa consulta também ordena o resultado pelo tipo de dados hierarchyid .

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

Aqui está o conjunto de resultados.

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

A hierarquia tem uma estrutura válida, mesmo que não seja internamente consistente. A Bahia é o único estado. Aparece na hierarquia ao mesmo nível da cidade de Brasília. Da mesma forma, a Estação McMurdo não tem um país/região pai. Os usuários devem decidir se esse tipo de hierarquia é apropriado para seu uso.

Adicione outra linha e selecione os resultados.

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

Isso demonstra mais possíveis problemas. Quioto pode ser inserido como nível /1/3/1/ , mesmo que não haja um nível /1/3/pai. E tanto Londres como Quioto têm o mesmo valor para o hierarchyid. Novamente, os usuários devem decidir se esse tipo de hierarquia é apropriado para seu uso e bloquear valores que são inválidos para seu uso.

Além disso, esta tabela não usou o topo da hierarquia '/'. Foi omitido porque não há um ancestral comum de todos os continentes. Você pode adicionar um adicionando todo o planeta.

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

Migrar de pai/filho para hierarchyid

A maioria das árvores é representada usando pai/filho. A maneira mais fácil de migrar de uma estrutura pai/filho para uma tabela usando hierarchyid é usar uma coluna temporária ou uma tabela temporária para controlar o número de nós em cada nível da hierarquia. 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.

Gerenciar uma árvore usando hierarchyid

Embora uma coluna hierarchyid não represente necessariamente uma árvore, um aplicativo pode facilmente garantir que represente isso.

Ao gerar novos valores, siga um destes passos:
- Mantenha o registo do último número do filho na linha pai.
- Calcule o último filho. Fazer isso de forma eficiente requer um índice de amplitude.
Imponha exclusividade criando um índice exclusivo na coluna, talvez como parte de uma chave de clustering. Para garantir que valores exclusivos sejam inseridos, siga uma das seguintes etapas:
- Detete falhas exclusivas de violação de chave e tente novamente.
- Determine a exclusividade de cada novo nó filho e insira-o como parte de uma transação serializável.

Exemplo usando a deteção de erros

No exemplo a seguir, o código de exemplo calcula o valor do novo filho EmployeeId e, em seguida, deteta qualquer violação de chave e retorna ao marcador INS_EMP para recalcular o valor EmployeeId da nova linha:

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

Exemplo usando uma transação serializável

O Org_BreadthFirst índice garante que a determinação @last_child usa uma busca de intervalo. Além de outros casos de erro que um aplicativo pode querer verificar, uma violação de chave duplicada após a inserção indica uma tentativa de adicionar vários funcionários com a mesma ID e, portanto, @last_child deve ser recalculado. O código a seguir calcula o novo valor de nó dentro de uma transação serializável:

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;

O código a seguir preenche a tabela com três linhas e retorna os resultados:

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

EXECUTE AddEmp 0x, 'Sariya';
GO

EXECUTE AddEmp 0x58, 'Mary';
GO

SELECT * FROM Org_T2

Aqui está o conjunto de resultados.

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

Impor uma árvore

Os exemplos anteriores ilustram como um aplicativo pode garantir que uma árvore seja mantida. Para estruturar uma árvore adotando restrições, pode-se criar uma coluna computada que defina o pai de cada nó com uma restrição de chave estrangeira referente ao ID da chave primária.

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

Esse método de imposição de uma relação é preferido quando o código que não é confiável para manter a árvore hierárquica tem acesso DML direto à tabela. No entanto, esse método pode reduzir o desempenho porque a restrição deve ser verificada em cada operação DML.

Encontre antepassados usando o CLR

Uma operação comum envolvendo dois nós em uma hierarquia é encontrar o menor ancestral comum. Essa tarefa pode ser escrita em Transact-SQL ou CLR, porque o tipo hierarchyid está disponível em ambos. Recomenda-se o CLR porque oferece melhor desempenho.

Use o seguinte código CLR para listar antepassados e encontrar o menor ancestral comum:

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

Para usar os ListAncestor métodos e CommonAncestor nos exemplos de Transact-SQL a seguir, crie a DLL e crie o HierarchyId_Operations assembly no SQL Server executando código semelhante ao exemplo a seguir:

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

Lista de antepassados

Criar uma lista de ancestrais de um nó é uma operação comum, por exemplo, para mostrar a posição em uma organização. Uma maneira de fazer isso é usando uma função de valor de tabela usando a HierarchyId_Operations classe definida anteriormente:

Utilização do Transact-SQL:

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

Exemplo de utilização:

DECLARE @h AS 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

Encontre o ancestral comum mais próximo

Usando a HierarchyId_Operations classe definida anteriormente, crie a seguinte função Transact-SQL para encontrar o ancestral comum mais baixo envolvendo dois nós em uma hierarquia:

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

Exemplo de utilização:

DECLARE @h1 AS HIERARCHYID, @h2 AS 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/

Subárvores de movimento

Outra operação comum é a movimentação de subárvores. O procedimento a seguir pega a subárvore de @oldMgr e a torna (incluindo @oldMgr) uma subárvore de @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

de referência do método de tipo de dados hierarchyid
Tutorial: Usando o tipo de dados hierarchyid
hierarchyid (Transact-SQL)

Comentários

Esta página foi útil?

Last updated on 2025-11-18