Lição 1: conversão de uma tabela em uma estrutura hierárquica

Artigo
07/15/2024

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Os clientes que possuem tabelas que usam autojunções para expressar relações hierárquicas podem converter as tabelas em uma estrutura hierárquica usando esta lição como guia. É relativamente fácil fazer a migração dessa representação para outra usando hierarchyid. Depois da migração, os usuários terão uma representação hierárquica compacta e fácil de entender, que poderá ser indexada de várias formas para proporcionar consultas eficientes.

Esta lição examina uma tabela existente, cria uma tabela contendo uma coluna hierarchyid , popula a tabela com os dados da tabela de origem e, depois, demonstra três estratégias de indexação. Eis os tópicos desta lição:

Pré-requisitos

Para concluir este tutorial, você precisará do SQL Server Management Studio, bem como acesso a um servidor que executa o SQL Server e um banco de dados do AdventureWorks.

Instalar o SQL Server Management Studio.
Instalar o SQL Server 2017 Developer Edition.
Baixar Bancos de dados de exemplo do AdventureWorks.

Instruções para restaurar bancos de dados no SSMS são encontradas aqui: Restaurar um banco de dados.

Examinar a estrutura atual da tabela Employee

O banco de dados de exemplo AdventureWorks2022 contém uma tabela Employee no esquema HumanResources . Para evitar alterar a tabela original, este passo cria uma cópia da tabela Employee nomeada EmployeeDemo. Para simplificar o exemplo, você copia só cinco colunas da tabela original. Então, você consulta a tabela HumanResources.EmployeeDemo para revisar como os dados são estruturados em uma tabela sem usar o tipo de dados hierarchyid .

Copiar a tabela Employee

Em uma janela Editor de Consultas, execute o código seguinte para copiar a estrutura de tabela e dados da tabela Employee em uma tabela nova nomeada EmployeeDemo. Como a tabela original já usa hierarchyid, essa consulta basicamente mescla a hierarquia para recuperar o gerente do funcionário. Em partes subsequentes desta lição, reconstruiremos essa hierarquia.

USE AdventureWorks2022;  
GO  
  if OBJECT_ID('HumanResources.EmployeeDemo') is not null
 drop table HumanResources.EmployeeDemo 

 SELECT emp.BusinessEntityID AS EmployeeID, emp.LoginID, 
  (SELECT  man.BusinessEntityID FROM HumanResources.Employee man 
	    WHERE emp.OrganizationNode.GetAncestor(1)=man.OrganizationNode OR 
		    (emp.OrganizationNode.GetAncestor(1) = 0x AND man.OrganizationNode IS NULL)) AS ManagerID,
       emp.JobTitle, emp.HireDate
INTO HumanResources.EmployeeDemo   
FROM HumanResources.Employee emp ;
GO

Examine a estrutura e os dados da tabela EmployeeDemo

Esta nova tabela EmployeeDemo representa uma tabela típica em um banco de dados existente que você pode querer migrar para uma nova estrutura. Em uma janela de Editor de Consultas, execute o código seguinte para mostrar como a tabela usa uma autojunção para exibir as relações de funcionário/gerente:

SELECT   
    Mgr.EmployeeID AS MgrID, Mgr.LoginID AS Manager,   
    Emp.EmployeeID AS E_ID, Emp.LoginID, Emp.JobTitle  
FROM HumanResources.EmployeeDemo AS Emp  
LEFT JOIN HumanResources.EmployeeDemo AS Mgr  
ON Emp.ManagerID = Mgr.EmployeeID  
ORDER BY MgrID, E_ID

Este é o conjunto de resultados.

MgrID Manager                 E_ID LoginID                  JobTitle  
NULL	NULL	                1	adventure-works\ken0	    Chief Executive Officer
1	adventure-works\ken0	    2	adventure-works\terri0   	Vice President of Engineering
1	adventure-works\ken0	   16	adventure-works\david0	  Marketing Manager
1	adventure-works\ken0	   25	adventure-works\james1	  Vice President of Production
1	adventure-works\ken0	  234	adventure-works\laura1	  Chief Financial Officer
1	adventure-works\ken0  	263	adventure-works\jean0	    Information Services Manager
1	adventure-works\ken0	  273	adventure-works\brian3	  Vice President of Sales
2	adventure-works\terri0	  3	adventure-works\roberto0	Engineering Manager
3	adventure-works\roberto0	4	adventure-works\rob0	    Senior Tool Designer
...

Os resultados continuam por um total de 290 linhas.

Observe que a cláusula ORDER BY fez com que a saída listasse os relatórios diretos de cada nível de administração junto. Por exemplo, todos os sete relatórios diretos de MgrID 1 (ken0) estão listados adjacentes uns aos outros. Embora não seja impossível, é muito mais difícil agrupar todos aqueles que eventualmente se reportem ao MgrID 1.

Popular uma tabela com os dados hierárquicos existentes

Essa tarefa cria uma tabela nova e a popula com os dados da tabela EmployeeDemo . Essa tarefa tem as seguintes etapas:

Crie uma nova tabela que contém uma coluna hierarchyid . Essa coluna pode substituir as colunas EmployeeID e ManagerID existentes. Entretanto, você manterá essas colunas. Isso porque os aplicativos existentes podem se referir a essas colunas e, também, para ajudar a compreender os dados depois da transferência. A definição da tabela especifica que OrgNode é a chave primária, exigindo que a coluna contenha valores exclusivos. O índice clusterizado da coluna OrgNode armazenará a data na sequência OrgNode .
Crie uma tabela temporária que será usada para localizar quantos funcionários se reportam diretamente a cada gerenciador.
Popule a nova tabela usando dados da tabela EmployeeDemo .

Para criar uma tabela chamada NewOrg

Em uma janela do Editor de Consultas, execute o seguinte código para criar uma tabela chamada HumanResources.NewOrg:

CREATE TABLE HumanResources.NewOrg  
(  
  OrgNode hierarchyid,  
  EmployeeID int,  
  LoginID nvarchar(50),  
  ManagerID int  
CONSTRAINT PK_NewOrg_OrgNode  
  PRIMARY KEY CLUSTERED (OrgNode)  
);  
GO

Criar uma tabela temporária chamada #Children

Crie uma tabela temporária chamada #Children com uma coluna chamada Num que conterá o número de filhos de cada nó:
```
CREATE TABLE #Children   
   (  
    EmployeeID int,  
    ManagerID int,  
    Num int  
);  
GO  
```
Adicione um índice que acelerará consideravelmente a consulta que popula a tabela NewOrg :
```
CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
GO  
```

Popular a tabela NewOrg

Consultas recursivas proíbem subconsultas com agregações. Em vez disso, popule a tabela #Children com o seguinte código, que usa o método ROW_NUMBER() para popular a coluna Num :

INSERT #Children (EmployeeID, ManagerID, Num)  
SELECT EmployeeID, ManagerID,  
  ROW_NUMBER() OVER (PARTITION BY ManagerID ORDER BY ManagerID)   
FROM HumanResources.EmployeeDemo  
GO

Examine a tabela #Children . Observe como a coluna Num contém números sequenciais para cada gerenciador.

SELECT * FROM #Children ORDER BY ManagerID, Num  
GO

Este é o conjunto de resultados.

EmployeeID	ManagerID	Num
1  	NULL  	1
2	     1    1
16	   1	  2
25	   1	  3
234	   1	  4
263	   1	  5
273	   1	  6
3	     2	  1
4	     3	  1
5	     3	  2
6	     3	  3
7	     3	  4

Popule a tabela NewOrg . Use os métodos GetRoot e ToString para concatenar os valores Num no formato hierarchyid e atualize a coluna OrgNode com os valores hierárquicos resultantes:

WITH paths(path, EmployeeID)   
AS (  
-- This section provides the value for the root of the hierarchy  
SELECT hierarchyid::GetRoot() AS OrgNode, EmployeeID   
FROM #Children AS C   
WHERE ManagerID IS NULL   

UNION ALL   
-- This section provides values for all nodes except the root  
SELECT   
CAST(p.path.ToString() + CAST(C.Num AS varchar(30)) + '/' AS hierarchyid),   
C.EmployeeID  
FROM #Children AS C   
JOIN paths AS p   
   ON C.ManagerID = P.EmployeeID   
)  
INSERT HumanResources.NewOrg (OrgNode, O.EmployeeID, O.LoginID, O.ManagerID)  
SELECT P.path, O.EmployeeID, O.LoginID, O.ManagerID  
FROM HumanResources.EmployeeDemo AS O   
JOIN Paths AS P   
   ON O.EmployeeID = P.EmployeeID  
GO

Uma coluna hierarchyid fica mais fácil de entender quando você a converte no formato de caractere. Examine os dados da tabela NewOrg executando o seguinte código, que contém duas representações da coluna OrgNode :
```
SELECT OrgNode.ToString() AS LogicalNode, *   
FROM HumanResources.NewOrg   
ORDER BY LogicalNode;  
GO  
```
A coluna LogicalNode converte a coluna hierarchyid em um formulário de texto mais legível que representa a hierarquia. Nas tarefas restantes, você usará o método ToString() para mostrar o formato lógico das colunas hierarchyid .
Descarte a tabela temporária, que não será mais necessária:
```
DROP TABLE #Children  
GO  
```

Otimizando a tabela NewOrg

A tabela NewOrd criada na tarefa Populando uma tabela com dados hierárquicos existentes contém todas as informações de funcionários e representa a estrutura hierárquica usando um tipo de dados hierarchyid . Essa tarefa adiciona índices novos para dar suporte às pesquisas na coluna hierarchyid .

A coluna hierarchyid (OrgNode) é a chave primária da tabela NewOrg . Quando a tabela foi criada, ela continha um índice clusterizado chamado PK_NewOrg_OrgNode para impor a exclusividade da coluna OrgNode . Esse índice clusterizado também oferece suporte a uma pesquisa primária detalhada da tabela.

Criar índice na tabela NewOrg para pesquisas eficientes

Para ajudar as consultas no mesmo nível na hierarquia, use o método GetLevel para criar uma coluna calculada que contém o nível na hierarquia. Em seguida, crie um índice composto no nível e em Hierarchyid. Execute o código a seguir para criar a coluna computada e o índice de amplitude primária:
```
ALTER TABLE HumanResources.NewOrg   
   ADD H_Level AS OrgNode.GetLevel() ;  
CREATE UNIQUE INDEX EmpBFInd   
   ON HumanResources.NewOrg(H_Level, OrgNode) ;  
GO  
```
Crie um índice exclusivo na coluna EmployeeID . Esta é uma pesquisa singleton tradicional de um único funcionário pelo número EmployeeID . Execute o seguinte código para criar um índice em EmployeeID:
```
CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
GO
```

Execute o código a seguir para recuperar dados da tabela na ordem de cada um dos três índices:

SELECT OrgNode.ToString() AS LogicalNode,  
OrgNode, H_Level, EmployeeID, LoginID  
FROM HumanResources.NewOrg   
ORDER BY OrgNode;  

SELECT OrgNode.ToString() AS LogicalNode,  
OrgNode, H_Level, EmployeeID, LoginID   
FROM HumanResources.NewOrg   
ORDER BY H_Level, OrgNode;  

SELECT OrgNode.ToString() AS LogicalNode,  
OrgNode, H_Level, EmployeeID, LoginID   
FROM HumanResources.NewOrg   
ORDER BY EmployeeID;  
GO

Compare os conjuntos de resultados para ver como a ordem está armazenada em cada tipo de índice. Seguem apenas as primeiras quatro linhas de cada saída.

Este é o conjunto de resultados.

Índice de profundidade primária: os registros de funcionário são armazenados adjacentes aos de seu gerente.

LogicalNode	OrgNode	H_Level	EmployeeID	LoginID
/	0x	0	1	adventure-works\ken0
/1/	0x58	1	2	adventure-works\terri0
/1/1/	0x5AC0	2	3	adventure-works\roberto0
/1/1/1/	0x5AD6	3	4	adventure-works\rob0
/1/1/2/	0x5ADA	3	5	adventure-works\gail0
/1/1/3/	0x5ADE	3	6	adventure-works\jossef0
/1/1/4/	0x5AE1	3	7	adventure-works\dylan0
/1/1/4/1/	0x5AE158	4	8	adventure-works\diane1
/1/1/4/2/	0x5AE168	4	9	adventure-works\gigi0
/1/1/4/3/	0x5AE178	4	10	adventure-works\michael6
/1/1/5/	0x5AE3	3	11	adventure-works\ovidiu0

Índice deEmployeeIDprimário: as linhas são armazenadas na sequência de EmployeeID .

LogicalNode	OrgNode	H_Level	EmployeeID	LoginID
/	0x	0	1	adventure-works\ken0
/1/	0x58	1	2	adventure-works\terri0
/1/1/	0x5AC0	2	3	adventure-works\roberto0
/1/1/1/	0x5AD6	3	4	adventure-works\rob0
/1/1/2/	0x5ADA	3	5	adventure-works\gail0
/1/1/3/	0x5ADE	3	6	adventure-works\jossef0
/1/1/4/	0x5AE1	3	7	adventure-works\dylan0
/1/1/4/1/	0x5AE158	4	8	adventure-works\diane1
/1/1/4/2/	0x5AE168	4	9	adventure-works\gigi0
/1/1/4/3/	0x5AE178	4	10	adventure-works\michael6
/1/1/5/	0x5AE3	3	11	adventure-works\ovidiu0
/1/1/5/1/	0x5AE358	4	12	adventure-works\thierry0

Observação

Para diagramas que mostram a diferença entre um índice de profundidade primária e um índice de amplitude primária, confira Dados hierárquicos (SQL Server).

Remover as colunas desnecessárias

A coluna ManagerID representa a relação funcionário/gerente, que é representada agora pela coluna OrgNode . Se outros aplicativos não precisarem da coluna ManagerID , considere removê-la usando a seguinte instrução:
```
ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
GO  
```
A coluna EmployeeID também é redundante. A coluna OrgNode identifica cada empregado de forma exclusiva. Se os outros aplicativos não precisarem da coluna EmployeeID , considere remover o índice e depois a coluna usando o seguinte código:
```
DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
GO  
```

Substituir a tabela original pela nova

Se sua tabela original continha outros índices ou restrições, adicione-os à tabela NewOrg .
Substitua a tabela antiga EmployeeDemo pela nova tabela. Execute o código a seguir para cancelar a tabela antiga e então renomeie a tabela nova com o nome antigo:
```
DROP TABLE HumanResources.EmployeeDemo ;  
GO  
sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
GO  
```
Execute o código a seguir para examinar a tabela final:
```
SELECT * FROM HumanResources.EmployeeDemo ;  
```

Próximas etapas

O próximo artigo ensina a criar e gerenciar dados em uma tabela hierárquica.

Vá até o próximo artigo para saber mais:

Próximas Etapas

Compartilhar via

Lição 1: conversão de uma tabela em uma estrutura hierárquica

Pré-requisitos

Examinar a estrutura atual da tabela Employee

Copiar a tabela Employee

Examine a estrutura e os dados da tabela EmployeeDemo

Popular uma tabela com os dados hierárquicos existentes

Para criar uma tabela chamada NewOrg

Criar uma tabela temporária chamada #Children

Popular a tabela NewOrg

Otimizando a tabela NewOrg

Criar índice na tabela NewOrg para pesquisas eficientes

Remover as colunas desnecessárias

Substituir a tabela original pela nova

Próximas etapas

Comentários

Recursos adicionais