334
Part III: Advanced T-SQL Data Types Querying Techniques
Indexing Strategies
There are two approaches when needing to index hierarchical data, and the index you apply
depends on which approach you take:■ (^) Depth-fi rst
■ (^) Breadth-fi rst
In a depth-fi rst approach, rows in a subtree are stored near each other. For example,
employee records are stored near their managers’ record. In a breadth-fi rst approach, rows
for each level in the hierarchy are stored together. For example, managers and their direct
reports are stored near each other.
Thus, in a depth-fi rst approach the index is applied so that all nodes in the subtree of a
node are colocated. Depth-fi rst indexes are effi cient for queries about the subtree, such as
“Find all fi les in this folder and its subfolders.”
CREATE UNIQUE INDEX OrgNode_Depth_First
ON HumanResources.Employee(OrganizationNode) ;
GO
In a breadth-fi rst index all direct children of a node are colocated. Breadth-fi rst indexes
are effi cient for queries about immediate children, such as “Find all employees who report
directly to this manager.”
CREATE CLUSTERED INDEX OrgNode_Breadth_First
ON HumanResources.Employee(OrganizationLevel) ;
GO
Whether to have depth-fi rst, breadth-fi rst, or both, and which to make the clustering key
(if any), depends on the relative importance of the above types of queries, and the relative
importance of SELECT versus DML operations.
Hierarchical Data Alternatives
Although the hierarchyid is a great way to work hierarchical data, you can use
alternatives where the hierarchyid is not available. Plenty of alternative methods exist,
but a couple of the more popular methods are CTEs and XML.Recursive CTE
Common Table Expression (CTE) was introduced in SQL Server 2005 as an alternative to
derived tables and an expansion of temporary results sets. A CTE is defi ned within the
execution scope of a single SELECT, INSERT, UPDATE, and DELETE statement. Much like a
derived table, a CTE is not stored as an object and lasts only for the duration of the query.c13.indd 334c13.indd 334 7/30/2012 4:47:05 PM7/30/2012 4:47:05 PM
http://www.it-ebooks.info