优化 NewOrg 表

您在使用现有分层数据填充表任务中创建的 HumanResources.NewOrd 表包含所有雇员的信息,该表使用 hierarchyid 数据类型表示层次结构。此任务添加了新的索引,以便支持对 hierarchyid 列的搜索。

聚集索引

hierarchyid 列 (OrgNode) 是 NewOrg 表的主键。此表创建时,其内包含了一个名为 PK_NewOrg_OrgNode 的聚集索引,用于强制实现 OrgNode 列的唯一性。此聚集索引还支持对表进行深度优先搜索。

非聚集索引

此步骤将创建两个非聚集索引,用于支持典型搜索。

为 NewOrg 表创建索引以提高搜索效率

  1. 若要改善在层次结构中同一级别的查询,可以使用 GetLevel 方法创建一个包含此层次结构中的此级别的计算列。然后,对此级别和 Hierarchyid 创建一个组合索引。运行下列代码以创建计算列和广度优先索引:

    ALTER TABLE HumanResources.NewOrg 
    ADD H_level AS OrgNode.GetLevel() ;
    CREATE UNIQUE INDEX EmpBFInd 
       ON HumanResources.NewOrg(H_level, OrgNode) ;
    GO
    
  2. EmployeeID 列创建一个唯一索引。即采用传统方式通过 EmployeeID 号单独查找一个雇员。运行下列代码以便对 EmployeeID 创建索引:

    CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;
    GO
    
  3. 运行下列代码以分别按照三个索引的顺序从表中检索数据:

    SELECT OrgNode.ToString() AS LogicalNode,
    OrgNode, H_Level, EmployeeID, LoginID, Title
    FROM HumanResources.NewOrg 
    ORDER BY OrgNode;
    
    SELECT OrgNode.ToString() AS LogicalNode,
    OrgNode, H_Level, EmployeeID, LoginID, Title
    FROM HumanResources.NewOrg 
    ORDER BY H_Level, OrgNode;
    
    SELECT OrgNode.ToString() AS LogicalNode,
    OrgNode, H_Level, EmployeeID, LoginID, Title
    FROM HumanResources.NewOrg 
    ORDER BY EmployeeID;
    GO
    
  4. 比较结果集以查看每类索引中的存储顺序。下面只显示了各输出的前四行。

    下面是结果集。

    深度优先索引:雇员记录存储在与相应经理的记录相邻的位置。

    LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
    /           0x         0       109        adventure-works\ken0     Chief Executive Officer
    /1/         0x58       1        12        adventure-works\terri0   Vice President of Engineering
    /1/1/       0x5AC0     2         3        adventure-works\roberto0 Engineering Manager
    /1/1/1/     0x5AD6     3         4        adventure-works\rob0     Senior Tool Designer
    

    广度优先索引:管理级别存储在一起。

    LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
    /           0x         0       109        adventure-works\ken0     Chief Executive Officer
    /1/         0x58       1        12        adventure-works\terri0   Vice President of Engineering
    /2/         0x68       1         6        adventure-works\david0   Marketing Manager
    /3/         0x78       1        42        adventure-works\jean0    Information Services Manager
    ...
    

    EmployeeID 优先索引:各行按照 EmployeeID 顺序存储。

    LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
    /6/4/13/12/ 0x961B7640 4       1          adventure-works\guy1     Production Technician - WC60
    /2/5/       0x6C60     2       2          adventure-works\kevin0   Marketing Assistant
    /1/1/       0x5AC0     2       3          adventure-works\roberto0 Engineering Manager
    /1/1/1/     0x5AD6     3       4          adventure-works\rob0     Senior Tool Designer
    
注意注意

有关显示深度优先索引和广度优先索引之间差异的关系图,请参阅使用 hierarchyid 数据类型(数据库引擎)

删除不需要的列

  1. ManagerID 列用于表示雇员/经理关系,现在由 OrgNode 列来表示。如果其他应用程序不需要 ManagerID 列,可以考虑使用下列语句删除该列:

    ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;
    GO
    
  2. EmployeeID 列也是冗余列。OrgNode 列可以唯一标识每个雇员。如果其他应用程序不需要 EmployeeID 列,可以考虑使用下列代码先删除索引再删除该列:

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

使用新表替换原始表

  1. 如果您的原始表包含任何其他索引或约束,请将它们添加到 NewOrg 表中。

  2. 将旧的 EmployeeDemo 表替换为新表。运行下列代码以删除旧表,然后使用旧表的名称重新命名新表:

    DROP TABLE HumanResources.EmployeeDemo ;
    GO
    sp_rename 'HumanResources.NewOrg', EmployeeDemo ;
    GO
    
  3. 运行下列代码以检查最终表:

    SELECT * FROM HumanResources.EmployeeDemo ;
    

课程中的下一个任务

摘要:将表转换为层次结构