MSDN Magazine - September 2008 - (Page 64)

/ /110/ /110/210/ /110/220/ /110/220/340/ /110/230/ /110/230/360/ /110/220/340/ Figure 7 Paths Used in the Sample Bill of Materials The remaining queries demonstrate adding a new bill to the system—in this case, assume that the company is including a 4GB memory stick as part of “Summer Bonus Promotion.” The code in Figure 6 inserts a new part, and the bill representing it, into the hierarchy. Now suppose that somebody points out that the “Summer Bo nus Package” bill should not be located directly beneath the total system bill, but rather under the Disk Drive module. This is an easy change to make in a relational database: update dbo.bill set parentBillID = 320 where billID = 507 go select * from dbo.bill where parentBillID = 320; go At the end of the summer promotion period, deleting this new bill is also simple: begin transaction delete from dbo.billPartList where billID=507; delete from dbo.bill where billID=507; delete from dbo.part where partID=45; commit; go select * from dbo.billPartList; select * from dbo.bill; are normally represented by integers, but decimal values maybe used as well. The or dered path must terminate with another single char /110/240/ /110/250/ acter (/). Ordered paths are not stored in the database in text form, however. Rather, they are mathematically hashed into binary values and those binary values are what is stored in the data pages. How then do we need to modify our existing database to use HierarchyID? Figure 7 shows some of the ordered paths used by the example bill. Since the parent/child relationship in my sample data is found in the bill table, that is a logical place to start. Rather than modify that table, I will add a new table called bill2. I will add a column named billPath, of the HierarchyID type. I store the parent/child relationships between bills—using ordered path format—in this column. And while I could at this point re move a column to navigate to a parent row, having this capability will still be helpful in many queries. Rather than store the parent ID as a static data field, however, I will show you how you can use a persisted, computed column based on the hierarchy ID for this type of navigation. The updated table creation code is as follows: create table dbo.bill2( billPath HierarchyID not null, billID smallint not null, parentBillPath as billPath.GetAncestor(1) persisted, descr varchar(50) not null); Taking Advantage of HierarchyID The HierarchyID data type is a CLRbased binary represen tation designed to store a compact, binary representation of an ordered path. Since it is a builtin data type there is no need to An ordered path looks a bit like a file path but uses numeric values instead of names. specifically activate the SQL/CLR functionality to use it. Hierar chyID is useful whenever you need to represent a nested relation ship between values where that relationship can be expressed in an ordered path syntax. An ordered path looks a bit like a file path, but instead of using directory and file names, numeric values are used. Just like any other parent/child relationship, all ordered paths must be anchored by a root node. In SQL Server 2008, a single character (/) is used to textually represent the root node. Elements with the ordered path 64 msdn magazine If you have not worked with data types based in the Microsoft® .NET Framework before, the method invocation in the definition of parentBillPath might seem unusual. The ability to invoke a method defined by the type, optionally passing it one or more parameters, greatly simplifies the code. Here you can see the HierarchyID type’s GetAncestor method. The HierarchyID stores the path in a compact binary form. You simply cannot reference some segment of that binary value and treat that as a billID, nor can you directly fetch some subsegment of the ordered path for a bill. For this, you can take advantage of the GetAncestor method. The parameter value here means “return the ordered path ending with this node’s parent.” The parameter value controls how far up in the ordered path the truncation occurs. So here you have the ordered path to the current bill’s parent. Figure 8 summarizes the methods available on the HierarchyID data type and some typical use cases for each method. Next, you need to create a primary key on the new table as well as its foreign key constraint: alter table dbo.bill2 add constraint pkBill2 primary key(billPath); alter table dbo.bill2 add constraint fkBill2Parent foreign key (parentBillPath) references dbo.bill2(billPath); In this case I am using HierarchyID values as both a primary key and foreign key. Remember that a HierarchyID represents a depthfirst 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.