Usando tipos de dados hierarchyid (Mecanismo de Banco de Dados)
O tipo de dados hierarchyid é fornecido pelo sistema. Use hierarchyid como tipo de dados para criar tabelas com uma estrutura hierárquica ou para fazer referência à estrutura hierárquica de dados em outro local. Use funções de hierarchyid para consultar e trabalhar com dados hierárquicos usando Transact-SQL.
Os dados hierárquicos são definidos como um conjunto de itens de dados mutuamente relacionados por relações hierárquicas. As relações hierárquicas existem onde um item de dados é o pai de outro item. Os dados hierárquicos são comuns em bancos de dados. Os exemplos incluem o seguinte:
Uma estrutura organizacional
Um sistema de arquivos
Um conjunto de tarefas em um projeto
Uma taxonomia de condições de linguagem
Um gráfico de links entre páginas da Web
Novidade no SQL Server 2008; o tipo hierarchyid facilita o armazenamento e a consulta de dados hierárquicos. O hierarchyid é aperfeiçoado para representar árvores, que são o tipo mais comum de dados hierárquicos.
Propriedades chave de hierarchyid
Um valor do tipo de dados hierarchyid 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ós n depende da média de fanout (o número médio de filhos de um nó). Para fanouts pequenos, o tamanho (0-7) é de aproximadamente 6*logAn bits, onde A é o fanout médio. Um nó em uma hierarquia organizacional de 100.000 pessoas com um fanout médio de 6 níveis usa cerca de 38 bits. Isso é arredondado para 40 bits, ou 5 bytes, para armazenamento.
A comparação está na ordem de profundidade
Dado dois valores hierarchyid a e b, a<b significa que a vem antes de b em uma passagem de profundidade da árvore. Índices em tipos de dados hierarchyid estão na ordem de profundidade e os nós que estão próximos um do outro são armazenados em uma passagem de profundidade próximos um do outro. Por exemplo, os filhos de um registro são armazenados adjacentes àquele registro.
Suporte a inserções e exclusões arbitrárias
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. 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 densidade. Porém, inserções entre dois nós produzirão valores hierarchyid com uma representação ligeiramente menos compacta.
Limitações de hierarchyid
O tipo de dados hierarchyid tem as seguintes limitações:
Uma coluna de tipo hierarchyid não representa automaticamente uma árvore. Depende de o aplicativo gerar e atribuir valores hierarchyid de tal modo que a relação desejada entre as linhas seja refletida nos valores. Alguns aplicativos não podem ter uma coluna do tipo hierarchyid para representar uma árvore. Talvez os valores sejam referências ao local em uma hierarquia definida em outra tabela.
Depende de o aplicativo gerenciar a simultaneidade gerando e atribuindo valores hierarchyid. Não há nenhuma garantia que valores hierarchyid em uma coluna sejam exclusivos a menos que o aplicativo use uma restrição chave exclusiva ou force sua exclusividade em sua própria lógica.
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 deverá fazer a consulta por descendentes antes de excluir os pais.
Indexando estratégias
Há duas estratégias para indexar dados hierárquicos:
Profundidade
Um índice de profundidade, linhas são armazenadas em uma subárvore próximas uma da outra. Por exemplo, todos os funcionários que se reportam a gerente são armazenados próximos do registro de seus gerentes.
.gif "Os nós são armazenados juntos.")
Amplitude
Uma amplitude 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 um do outro.
.gif "Cada nível de hierarquia é armazenado junto.")
Exemplos
O método GetLevel() pode ser usado para criar uma ordem por amplitude. No exemplo seguinte, são criados índices por amplitude e por profundidade:
USE AdventureWorks2008R2 ;
GO
CREATE TABLE Organization
(
BusinessEntityID hierarchyid,
OrgLevel as BusinessEntityID.GetLevel(),
EmployeeName nvarchar(50) NOT NULL
) ;
GO
Em um índice por profundidade todos os nós na subárvore de um nó ficam cossituados. Índices por profundidade são portanto eficientes para responder consultas sobre subárvores, como "Localizar todos os arquivos nesta pasta e subpastas""""""""".
CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization(OrgLevel,BusinessEntityID) ;
GO
CREATE UNIQUE INDEX Org_Depth_First
ON Organization(BusinessEntityID) ;
GO
Em um índice por amplitude todos os filhos diretos de um nós ficam cossituados. Í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".
Ter opções por profundidade, por amplitude, ou ambas, e por agrupamento chave (se houver), depende da importância relativa dos tipos de consultas anteriores e da importância relativa de operações SELECT versus DML. Para obter um exemplo detalhado de indexação de estratégias, consulte Tutorial: Usando o tipo de dados hierarchyid.
Quando usar alternativas para hierarchyid
As duas alternativas para hierarchyid para representar dados hierárquicos são:
Pai/filho
XML
Ahierarchyid é geralmente superior a essas alternativas. Porém, a seguir há situações específicas detalhadas em que as alternativas são provavelmente superiores.
Pai/filho
Ao usar 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 AdventureWorks2008R2 ;
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 comuns
Consultas de subárvore são significativamente mais rápidas com hierarchyid.
Consultas de descendente direto são ligeiramente mais lentas com hierarchyid.
A movimentação de nós é mais lenta com 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.
Pai/Filho pode ser superior quando existem as seguintes:
O tamanho da chave é muito crítico. Para o mesmo número de nós, um valor hierarchyid é igual ou maior que um valor família inteiro (smallint, int, bigint). 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.
Consultas raramente examinam por seções da hierarquia. Em outras palavras, se consultas normalmente consultam apenas um único ponto na hierarquia. Nesses casos, o colocal não é importante. Por exemplo, o Pai/Filho será superior se a tabela de organização só for usada para executar folha de pagamento para funcionários individuais.
Subárvores de não folha mudam frequentemente e o desempenho é muito importante. Em uma representação pai/filho, alterando o local de uma linha em uma hierarquia afeta uma linha única. Alterando o local de uma linha em um uso hierarchyid afeta n linhas, onde n é número de nós na subárvore que é modificada.
Se essas subárvores de não folha frequentemente mudarem e o desempenho for muito importante, mas a maioria das mudanças está em um nível bem definido da hierarquia, considere dividir os níveis superiores e inferiores em duas hierarquias. Isso faz todas as mudanças em níveis de folha da hierarquia mais alta. Por exemplo, considere uma hierarquia de sites hospedados por um serviço. Sites contêm muitas páginas organizadas de uma maneira hierárquica. Sites hospedados poderiam ser movidos a outros locais na hierarquia do site, mas as páginas subordinadas raramente seriam reorganizadas. Isso poderia ser representado por:
CREATE TABLE HostedSites ( SiteId hierarchyid, PageId hierarchyid ) ; GO
XML
Um documento XML é uma árvore e, portanto, uma instância de tipo de dados XML única pode representar uma hierarquia completa. No SQL Server quando um índice XML é criado, valores hierarchyid são internamente usados para representar a posição na hierarquia.
Usar um tipo de dados XML pode ser vantajoso quando todos os seguintes itens forem verdadeiros:
A hierarquia completa é sempre armazenada e recuperada.
Os dados são consumidos no formato XML pelo aplicativo.
Pesquisas de predicado são extremamente limitadas e não têm de desempenho crítico.
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:
CREATE TABLE XMLOrg
(
Orgid int,
Orgdata xml
) ;
GO
Migrando de Pai/Filho para hierarchyid
Atualmente, a maioria das árvores é representada usando Pai/Filho. O modo mais fácil para migrar de uma estrutura Pai/Filho a uma tabela que usa hierarchyid, é usar uma coluna temporária ou uma tabela temporária para manter o controle do 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 de Tutorial: Usando o tipo de dados hierarchyid.
Transformações de consulta em hierarchyid
Para maximizar o desempenho de consultas de hierarquias, o SQL Server executa automaticamente três transformações de consultas que envolvem hierarchyid. O resultado dessas transformações pode ser consultado na saída do plano de execução para consultas transformadas.
GetAncestor é transformado em uma varredura de intervalo e em um predicado residual
GetAncestor(n) fornece o nth ancestral de um nó. Isso é útil quando a relação precisa (pai, filho, avô etc.) entre dois nós é necessária, em contraste com o mais geral IsDescendantOf.
Por exemplo, execute a consulta a seguir para encontrar todos os funcionários cujo gerente direto é @value:
DECLARE @value hierarchyid ;
SELECT * FROM AdventureWorks2008R2.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@value) = 1;
Consulte também