Usando tipos de dados hierarchyid (Mecanismo de Banco de Dados)

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 AdventureWorks ;
GO

CREATE TABLE ParentChildOrg
   (
    EmployeeID int PRIMARY KEY,
    ManagerId int REFERENCES ParentChildOrg(EmployeeID),
    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 co-local 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 freqüentemente 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 freqüentemente 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
  

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

Dado E como uma coluna ou uma variável, E.IsDescendantOf(c) é transformado em busca de intervalos. Isso reduz significativamente o custo de encontrar os descendentes. Se houver um índice por profundidade em E, essa transformação ajuda porque todos os descendentes de E ficam co-situados. Por exemplo, o trecho de código EmployeeId.IsDescendantOf(@value) é executado como EmployeeId >= @Value AND EmployeeId <= @Value.DescendantLimit(). DescendantLimit é um método interno que determina o limite superior mínimo de todos os possíveis descendentes de um nó. Observe que @value não tem de ser um parâmetro. Poderia ser uma coluna, talvez de uma condição de junção.

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:

SELECT * FROM Employees WHERE EmployeeId.GetAncestor(1) = @value

Isso é transformado em um exame do intervalo de descendentes de @value, com o predicado original como um resíduo. O código é transformado no seguinte:

SELECT * FROM Employees 
WHERE 
   EmployeeId >= @Value AND EmployeeId <= @value.DescendantLimit() 
   AND EmployeeId.GetAncestor(1) = @value

O efeito disso é limitar o exame à subárvore de @value.

Na consulta anterior, se @value estiver nos níveis superiores da árvore, a otimização anterior não reduzirá significativamente o número de linhas examinadas. Quando as perguntas sobre nth ancestral forem comuns, os aplicativos deverão criar um índice por amplitude conforme previamente descrito.

Quando um índice por amplitude estiver presente, a consulta anterior será transformada posteriormente no seguinte:

SELECT * FROM Employees 
WHERE 
   EmployeeId >=@value AND EmployeeId <= @Value.DescendantLimit() 
   AND @value.GetLevel()+1 = EmployeeId.GetLevel()

A última linha (que contém os métodos GetLevel) se torna uma pesquisa de índice no índice por amplitude. Se EmployeeId for a chave de clustering, então ela será a segunda coluna no índice por amplitude, e os dois predicados se tornarão um índice de pesquisa que especificarão exatamente as subordinações diretas dos n co-situados de @value.

As transformações GetAncestor não são limitadas a consultas de pais diretos. O argumento para GetAncestor pode ser qualquer variável ou constante.