No último post da série sobre as novidades do SQL Server 2008 abordei os aprimoramentos para os data types de data e hora. Hoje veremos o novo tipo Hierarchyid e sua aplicação.
HIERARCHYID
O novo data type Hierarchyid permite armazenamento estruturado como uma hierarquia de relacionamento entre os dados.
Existem duas formas de pensar sobre o armazenamento da sua hierarquia, conforme as ilustrações a seguir:
Profundidade
Na chamada indexação por profundidade linhas são armazenadas em uma subárvore próximas umas das outras. Um bom exemplo para imaginarmos esse cenário é a relação entre gerentes e funcionários de uma empresa onde o armazenamento será próximo já que os funcionários se reportarão diretamente a algum gerente, da mesma forma que os gerentes a um diretor e assim por diante.
Amplitude
Neste tipo de estratégia as linhas de cada nível da hierarquia são armazenadas juntas, revendo o exemplo acima os funcionários que se reportam para um mesmo gerente são armazenados próximos uns dos outros.
Vejamos agora um exemplo com uma tabela simples para ilustrar os conceitos apresentados. A seguir temos o código para criação do exemplo:
CREATE TABLE Equipe ( Lider hierarchyid, ReportaPara as Lider.GetLevel(), ColaboradorId int UNIQUE NOT NULL, NomeColaborador nvarchar(50) NOT NULL ) ; GO
Vamos inserir alguns registros utilizando o código descrito a seguir:
INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (hierarchyid::GetRoot(), 1, 'Lider A'); GO INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (hierarchyid::GetRoot().GetDescendant(null, null), 2, 'Desenv A1'); GO INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (hierarchyid::GetRoot().GetDescendant(null, null), 3, 'Desenv A2'); GO INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (hierarchyid::GetRoot().GetDescendant(null, null), 4, 'Desenv A3'); GO
A sintaxe hierarchyid::GetRoot().GetDescendant(null, null) informa ao SQL Server que queremos obter o descendente derivado de Root, no caso, como temos apenas um registro inserido no Root (linhas 1 e 2 do código acima) as linhas serão relacionadas a esse registro.
Se quiséssemos inserir outro elemento derivado de Root, bastaria utilizar o seguinte código:
INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (hierarchyid::GetRoot(), 100, 'Lider B'); GO
Agora utilizaremos outra abordagem para inserimos alguns registros com outro nível de hierarquia.
DECLARE @Lider hierarchyid, @lc hierarchyid SELECT @Lider = Lider FROM Equipe WHERE colaboradorId = 2 INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (@Lider.GetDescendant(@lc, NULL), 101, 'Desenv A11'); INSERT Equipe (Lider, ColaboradorId, NomeColaborador) VALUES (@Lider.GetDescendant(@lc, NULL), 102, 'Desenv A12'); GO
Para visualizarmos o resultados vamos executar a query a seguir:
SELECT Lider.ToString(), * FROM Equipe
Repare que utilizei o método ToString() para fazer a conversão do tipo hierárquico para uma notação mais legível. Para visualizarmos todos os lideres do primeiro nível podemos utilizar a seguinte query:
SELECT Lider.ToString() AS OLider, * FROM Equipe WHERE Lider = hierarchyid::GetRoot() ; GO
A query a seguir retorna todos as linhas derivadas do colaborador com o Id 2:
DECLARE @Colaborador hierarchyid SELECT @Colaborador = Lider FROM Equipe WHERE ColaboradorId = 2; SELECT Lider.ToString() AS Colaborador, * FROM Equipe WHERE Lider.GetAncestor(1) = @Colaborador
Como vimos o novo data type Hierarchiyd permite um armazenamento inteligente e semântico para dados estruturados hierarquicamente, as aplicações são inúmeras. Espero que tenham gostado. Até o próximo post.
Leandro, para que serve o campo "ReportaPara"?
Você vai aprofundar um pouco mais na HierarchyId?
Fala Giovanni,
Apesar do campo [b]Lider[/b] definir a hierarquia, o campo [b]ReportaPara[/b] poderia ser usado para criar uma ordenação de amplitude através de um índice, já que no exemplo a idéia seria manter os registros correlatos próximos na hierarquia.
O código para criação do índice ficaria mais ou menos assim:
[b]CREATE CLUSTERED INDEX CadeiaHierarquica
ON Equipe(ReportaPara, ColaboradorId);
GO[/b]
A série de posts sobre o SQL Server 2008 tem o objetivo de dar um caminho introdutório, mas penso em tratar alguns dos assuntos em detalhes posteriormente.
Abraços!
Leandro Daniel
Hello, your articles here SQL Server 2008 (parte 5) – Hierarchyid Data Type | Leandro Daniel to write well, thanks for sharing!