MSDN Magazine - September 2008 - (Page 68)

Figure 10 Moving a Bill begin begin transaction declare @oldPath hierarchyID,@newRoot hierarchyID; declare @newPath hierarchyID; declare @billIDToMove int = 251; declare @billIDToMoveTo int = 320; select @oldPath = billPath from dbo.bill2 where BillID = @billIDToMove; select @newRoot = b.billPath from dbo.bill2 b where billID = @billIDToMoveTo select @newPath = @oldPath.GetReparentedValue( @oldPath.GetAncestor(1),@newRoot); update dbo.bill2 set billPath = @newPath output inserted.* where billID = 251; commit; end; change the billID (see Figure 10). The critical thing to remember about using GetReparentedValue is that calling it does not in and of itself affect any changes to values persisted in a table. Instead, you need to use an update to affect that change. Deleting a part and its matchingID bills can be either a fairly simple process or it can require substantial effort. Much depends on whether the bill being deleted is the parent of another bill. If not, as in the case of the Summer Bonus bill, you can just delete the bill and the part: begin declare @partToDelete int = 45; begin transaction delete from dbo.bill2 where billID in ( select billID from dbo.billPartList bpl join dbo.partList pl on bpl.partListID = pl.partListID join dbo.part p on pl.partID = p.partID where p.partID = @partToDelete); delete from dbo.part where partID = @partToDelete; commit; end; I am looking for all the root node’s immediate descendants. Since paths are ordered by value, the bill with the maximum value for the HierarchyID will be the righthandmost instance. Knowing the righthandmost peer node, I now need to create a new path adjacent to it. This is where GetDescendant comes into use. The parameters to this method control where the new path is created. When I leave the second parameter in the list null here, I am say ing “create the node to the right of the first parameter:” select @newBillPath =@root.GetDescendant(@newBillPath,null); Now I have a path for the new bill, and that path will have a value for newBillID, so I just need to extract that. The tablevalued func tion added earlier can help: select @newBillID = nodeValue from dbo.Vectorize(@newBillPath) where position = @newBillPath.GetLevel(); Deleting a bill that is the parent of one or more other bills in a design like the one presented here requires more complex code. The issue is that I have a foreign key constraint which requires each bill to have an immediate parent. If I try to delete a parent bill, an error is raised. To fix this, I must first move any children to a bill that will survive. To do that, I will have to temporarily drop the foreign key constraint and compute paths for the new nodes us ing the GetReparentedValue method. Wrapping Up Finally, I can finish this transaction by inserting the new bill into the dbo.bill2 table and inserting the billID and the partListID into the dbo.billPartList table: insert into dbo.bill2 output inserted.* values (@newBillPath,@newBillID,'Summer Bonus Package'); insert into dbo.billPartList(billID,partListID) output inserted.* values (@newBillID,@mpl); commit; end; The new HierarchyID type in SQL Server 2008 provides compact data types with the ability to store and work with nodeidentifying ordered paths. Almost any system that relies on a hierarchical data model can be implemented using this new data type. The primary benefits are that you can reduce amount of data physically stored on disk and write less complex queries. Note that you can still take advantage of the native SQL Server support for cascading referential integrity constraints with this type as well. The key point to remember is that unlike traditional approaches to modeling a hierarchy using scalar values for a child’s ID and a parent’s ID, with HierarchyID, each node will contain its complete path as its identity.  Kent tegels is the Data Curriculum Technical Lead at Developmentor. He develops and teaches classes for SQL Server, .NET, and XML. To move the Summer Bonus bill from being a direct child of the Super X100 Home Theatre System to being a child of the Disk Drive Module is not especially difficult provided that you do not want to 68 msdn magazine 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.