• Article

Optimizing the NewOrg Table

The HumanResources.NewOrd table that you created in the Populating a Table with Existing Hierarchical Data task contains all the employee information, and represents the hierarchical structure by using a hierarchyid data type. This task adds new indexes to support searches on the hierarchyid column.

Clustered Index

The hierarchyid column (OrgNode) is the primary key for the NewOrg table. When the table was created, it contained a clustered index named PK_NewOrg_OrgNode to enforce the uniqueness of the OrgNode column. This clustered index also supports a depth-first search of the table.

Nonclustered Index

This step creates two nonclustered indexes to support typical searches.

To index the NewOrg table for efficient searches

  1. To help queries at the same level in the hierarchy, use the GetLevel method to create a computed column that contains the level in the hierarchy. Then, create a composite index on the level and the Hierarchyid. Run the following code to create the computed column and the breadth-first index:

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

  2. Create a unique index on the EmployeeID column. This is the traditional singleton lookup of a single employee by EmployeeID number. Run the following code to create an index on EmployeeID:

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

  3. Run the following code to retrieve data from the table in the order of each of the three indexes:

    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. Compare the result sets to see how the order is stored in each type of index. Only the first four rows of each output follow.

    Here is the result set.

    Depth-first index: Employee records are stored adjacent to their manager.

    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

    Breadth-first index: Management levels are stored together.

    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-first index: Rows are stored in EmployeeID sequence.

    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

Note

For diagrams that show the difference between a depth-first index and a breadth-first index, see Using hierarchyid Data Types (Database Engine).

To drop the unnecessary columns

  1. The ManagerID column represents the employee/manager relationship, which is now represented by the OrgNode column. If other applications do not need the ManagerID column, consider dropping it by using the following statement:

    ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;
GO

  2. The EmployeeID column is also redundant. The OrgNode column uniquely identifies each employee. If other applications do not need the EmployeeID column, consider dropping the index and then the column by using the following code:

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

To replace the original table with the new table

  1. If your original table contained any additional indexes or constraints, add them to the NewOrg table.

  2. Replace the old EmployeeDemo table with the new table. Run the following code to drop the old table, and then rename the new table with the old name:

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

  3. Run the following code to examine the final table:

    SELECT * FROM HumanResources.EmployeeDemo ;