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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Os clientes que têm tabelas usando associações automáticas para expressar relações hierárquicas podem converter suas tabelas em uma estrutura hierárquica usando esta lição como guia. É relativamente fácil migrar dessa representação para uma usando hierarchyid. Após a migração, os usuários terão uma representação hierárquica compacta e de fácil compreensão, que pode ser indexada de várias maneiras para consultas eficientes.

Esta lição, examina uma tabela existente, cria uma nova tabela contendo uma coluna hierarchyid , preenche a tabela com os dados da tabela de origem e, em seguida, demonstra três estratégias de indexação. Este artigo inclui as seções a seguir:

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 o Bancos de dados de exemplo do AdventureWorks2017.

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

Examinar a estrutura atual da tabela de funcionários

O banco de dados de exemplo AdventureWorks2022 contém uma tabela Employee no esquema HumanResources . Para evitar alterar a tabela original, esta etapa faz uma cópia da tabela Employee chamada EmployeeDemo. Para simplificar o exemplo, copie apenas cinco colunas da tabela original. Em seguida, consulte a tabela HumanResources.EmployeeDemo para examinar como os dados são estruturados em uma tabela sem usar o tipo de dados hierarchyid .

Copiar a tabela Employee

1. Em uma janela do Editor de Consultas, execute o código a seguir para copiar a estrutura da tabela e os dados da tabela Employee para uma nova tabela chamada EmployeeDemo. Como a tabela original já usa hierarchyid, essa consulta basicamente nivela a hierarquia para recuperar o gerente do funcionário. Nas partes seguintes desta lição, estaremos reconstruindo 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

• Essa nova tabela EmployeeDemo representa uma tabela típica em um banco de dados existente que talvez você queira migrar para uma nova estrutura. Em uma janela do Editor de Consultas, execute o código a seguir para mostrar como a tabela usa uma associação automática para exibir as relações 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 para 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 gerenciamento juntos. Por exemplo, todos os sete relatórios diretos do 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 reportam ao MgrID 1.

Preencher uma tabela com dados hierárquicos existentes

Essa tarefa cria uma nova tabela e a preenche com os dados na tabela EmployeeDemo . Essa tarefa pode incluir as seguintes etapas:

• Crie uma nova tabela que contenha uma coluna hierarchyid . Esta coluna pode substituir as colunas EmployeeID e ManagerID existentes. No entanto, você manterá essas colunas. Isso ocorre porque os aplicativos existentes podem se referir a essas colunas e também para ajudá-lo a entender os dados após a transferência. A definição de tabela especifica que OrgNode é a chave primária, o que requer que a coluna contenha valores exclusivos. O índice clusterizado na coluna OrgNode armazenará a data na sequência OrgNode.
• Crie uma tabela temporária que seja usada para controlar quantos funcionários se reportam diretamente a cada gerente.
• Preencha a nova tabela usando dados da tabela EmployeeDemo .

Para criar uma nova tabela chamada NewOrg

• Em uma janela do Editor de Consultas, execute o seguinte código para criar uma nova 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  
  

Crie uma tabela temporária chamada #Children

1. Crie uma tabela temporária chamada #Children com uma coluna chamada Num que conterá o número de filhos para cada nó:

   CREATE TABLE #Children   
      (  
       EmployeeID int,  
       ManagerID int,  
       Num int  
   );  
   GO  
   

2. Adicione um índice que acelerará significativamente a consulta que preenche a tabela NewOrg :

   CREATE CLUSTERED INDEX tmpind ON #Children(ManagerID, EmployeeID);  
   GO  
   

Preencher a tabela NewOrg

1. Consultas recursivas proíbem subconsultas com agregações. Em vez disso, preencha a tabela #Children com o código a seguir, que usa o método ROW_NUMBER() para preencher a coluna Núm:

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

2. Revise a tabela #Children. Observe como a coluna Num contém números sequenciais para cada gerente.

   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
   

3. Preencha 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 
   

4. Uma coluna hierarchyid é mais compreensível quando você a converte para o formato de caractere. Revise os dados na tabela NewOrg executando o código a seguir, 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 para mostrar o ToString() formato lógico das colunas hierarchyid .

5. Solte a tabela temporária, que não é mais necessária:

   DROP TABLE #Children  
   GO  
   

Otimizando a tabela NewOrg

A tabela NewOrd que você criou na tarefa Preenchendo uma Tabela com Dados Hierárquicos Existentes contém todas as informações do funcionário e representa a estrutura hierárquica usando um tipo de dados hierarchyid . Esta tarefa adiciona novos índices para dar suporte a pesquisas na coluna hierarchyid .

A coluna hierarchyid (OrgNode) é a chave primária para a 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 profunda da tabela.

Criar índice na tabela NewOrg para pesquisas eficientes

1. Para ajudar as consultas no mesmo nível na hierarquia, use o método GetLevel para criar uma coluna computada que contenha o nível na hierarquia. Em seguida, crie um índice composto no nível e no Hierarchyid. Execute o código a seguir para criar a coluna computada e o índice de amplitude-primeira:

   ALTER TABLE HumanResources.NewOrg   
      ADD H_Level AS OrgNode.GetLevel() ;  
   CREATE UNIQUE INDEX EmpBFInd   
      ON HumanResources.NewOrg(H_Level, OrgNode) ;  
   GO  
   

2. Crie um índice exclusivo na coluna EmployeeID . Esta é a pesquisa tradicional de um único funcionário por número EmployeeID . Execute o seguinte código para criar um índice em EmployeeID:

   CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;  
   GO
   

3. 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  
   

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

   Este é o conjunto de resultados.

   Índice de profundidade em primeiro lugar: os registros dos funcionários são armazenados ao lado do 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
   

   EmployeeID-first index: as linhas são armazenadas na sequência 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).

Soltar as colunas desnecessárias

1. A coluna ManagerID representa a relação funcionário/gerente, que agora é representada pela coluna OrgNode . Se outros aplicativos não precisarem da coluna ManagerID , considere descartá-la usando a seguinte instrução:

   ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;  
   GO  
   

2. A coluna EmployeeID também é redundante. A coluna OrgNode identifica exclusivamente cada funcionário. Se outros aplicativos não precisarem da coluna EmployeeID , considere descartar o índice e, em seguida, a coluna usando o seguinte código:

   DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;  
   ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;  
   GO  
   

Substitua a tabela original pela nova tabela

1. Se a tabela original contiver índices ou restrições adicionais, adicione-os à tabela NewOrg .

2. Substitua a tabela EmployeeDemo antiga pela nova tabela. Execute o seguinte código para descartar a tabela antiga e, em seguida, renomeie a nova tabela com o nome antigo:

   DROP TABLE HumanResources.EmployeeDemo ;  
   GO  
   sp_rename 'HumanResources.NewOrg', 'EmployeeDemo' ;  
   GO  
   

3. Execute o seguinte código 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