MSDN Magazine - September 2008 - (Page 66)

Figure 8 HierarchyID Methods Method GetAncestor Returns A HierarchyID representing an ordered path for a parent or higher-level element. A HierarchyID representing the path of a child, grandchild, or later child of the current node. A 16-bit integer value representing the total depth of the ordered path. A HierarchyID for an ordered by path with zero elements. 1 if the ordered path passed in as a parameter is a child of the calling instance. A HierarchyID instance. Parameter Values An integer indicating the number of levels above the level to traverse up in the current ordered path. Two HierarchyID instances, either or both of which may be null, constraining the potential child returned. None. None. A HierarchyID instance. Purpose Finds the parent, grandparent, or higher-level element in the ordered path for this instance. Gets a path for inserting a new element at some depth into the current ordered path. Determines whether two ordered paths have the same depth. Finds the absolute root for any ordered path. Determines whether a given HierarchyID is a child of another instance. Creates a HierarchyID instance from a given path. Implicitly called whenever a HierarchyID instance is set to a string. Moves one or more row values from one parent ordered path to another. Analyzes the ordered path of a HierarchyID. GetDescendent GetLevel GetRoot IsDescendantOf Parse The textual representation of an ordered path. The current ordered path as a HierarchyID and the targeted ordered path, also as a HierarchyID. None. GetReparentedValue A HierarchyID representing a complete ordered path when moving the current item from one path to another. The textual representation of a HierarchyID’s ordered path. ToString in any hierarchy by calling the GetRoot method as a static mem ber of the type. Now when I insert firstlevel nodes, they will still refer to a parent within the current table. So just how do you migrate the existing bills from dbo.bill to dbo.bill2? All you really need to do is modify the previously shown query that selects the hierarchy path so that it casts that path to a HierarchyID and then inserts the data into dbo.bill2. Here is the TSQL code: with c(billID,[path],[descr]) as ( select b.billID,'/'+CAST(b.billID as varchar(max)),descr from dbo.bill b where parentBillID is null union all select b.billID,c.path+'/'+CAST(b.billID as varchar(max)),b.descr from dbo.bill b join c on b.parentBillID = c.billID) insert into dbo.bill2(billpath,billID,descr) select path+'/' as [path],billID,descr from c order by c.path; Figure 9 The First 15 Rows of dbo.bill2 ordered path. Therefore, values will vary most between peers and less between a parent and child path. When using a HierarchyID value for the clustered index on a table, nodes along the same path will be stored closer together than nodes on other paths. In many cases, this is a good design because you want to query a given path for all of its children. However, if you are more frequently querying for specific nodes, you might want to create the clustered index on a column derived from GetLevel. This particular implementation introduces a subtle problem. Be cause I am basing a foreign key constraint on a path rather than a value, I need an existing value in the table to anchor on. All chains of bills will require an anchor and, thankfully, they can all use the same one. The HierarchyID GetRoot method is actually the perfect way to solve this problem. Consider the following statement: insert into dbo.bill2(billPath,billID,descr) values (hierarchyID::GetRoot(),0,'All Bills'); You will now also want to update the foreign key relationship with dbo.BillPartList as follows: alter table dbo.billPartList drop constraint fkBillPartList_Bill go alter table dbo.billPartList add constraint fkBillPartList_Bill2 foreign key(billPath) references dbo.bill2(billPath) on delete cascade on update no action; Figure 9 shows the contents of dbo.Bill2. Testing the HierarchyID Implementation Here I am creating a bill that represents the highest possible root 66 msdn magazine With the tables created and populated and with constraints in place, I can rewrite the previous test queries to use the new Hier SQL Server 2008

Table of Contents Feed for the Digital Edition of MSDN Magazine - September 2008

MSDN Magazine - September 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Cutting Edge Hierarchy ID New Features for Microsoft SQL Server 2008 Prism Data Services Advanced WPF Test Run Security Briefs Foundations { End Bracket }

MSDN Magazine - September 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.