SQL Server 2008 - HierarchyID - Part II

Hi ! are you interested to know more about the HierarchyID then grab a bag of chips for yourself and read on. If you haven't read about the HierarchyID then it is recommended that go to my last post SQL Server 2008 - HierarchyID - Part I and read that first.

HierarchyID is definitely a nice data type to store the hierarchies. In day to day business most of the operation are quite complex and they look like a binary tree most of the times. To quote a good example in the manufacturing industry the BOM (Bill of Materials) play a very vital role which again comprises of Materials which are created out other materials.

Let me share some of my past experience, I was working with one of the Electrical and Electronic Equipment Manufacturing Company's IT department few years back . I was constantly challenged to discover a new way in which we can represent a BOM or maintain a tree of Vendors who would take the job on sub-contract and deliver the semi-finished goods. When we have to track the shipment of raw materials to these vendor who have taken it for processing or tracking back the shipments of finished or semi-finished goods received from these vendors which would often be in small chunks makes the things quite complex. I found that in day to day business the things become complex and the pattern that emerges is a super complex tree.

So finally I find this data type to help in these kind of scenario. Reparenting is one of the major things that happen to these trees. For example : As per my above scenario when we are tracking the semi-finished goods under one BOM and then many a times due to the time delay from the vendors the management takes a decision that the semi-finished component (X) will now be used in the manufacturing project (Z) because the Time to Market is less for the project (Z) and when we get the second shipment we will compensate for this project so when we have to shift a complete tree under some other head, it was a challenge.

Reparenting happens in the organization hierarchy also for example: The Sales Manager (North America) has been promoted to Regional Sales Director (Europe) then again the organization's structure is shuffled.

So here is a code sample that helps you do that. It is a kind recommendation to design the Database structure in such a manner that the requirement of Reparenting is minimal, this is not because SQL Server can't handle it, SQL Server 2008 is very well capable of handling it but if some query is misfired due to Human Error then it hardly takes anytime to turn a Data Warehouse to a Data Junkyard.

Code Sample to Move Org

 

CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) ) AS BEGIN DECLARE @nold HierarchyID DECLARE @nnew HierarchyID SELECT @nold = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @oldMgr ; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @nnew = OrgNode FROM HumanResources.EmployeeDemo WHERE LoginID = @newMgr ; SELECT @nnew = @nnew.GetDescendant(max(OrgNode), NULL) FROM HumanResources.EmployeeDemo WHERE OrgNode.GetAncestor(1)=@nnew ; UPDATE HumanResources.EmployeeDemo SET OrgNode = OrgNode.Reparent(@nold, @nnew) WHERE @nold.IsDescendant(OrgNode) = 1 ; COMMIT TRANSACTION END ;

 

The following code sample helps determine the MAX(Child) which could be passed to GetDescendant() function to get the new child position.

Create Function GetMyMaxChild(@ManagerID as BigInt) Returns HierarchyID BEGIN Declare @ManagerNode HierarchyID Declare @MaxChild HierarchyID  --Get the ManagerNode Select @ManagerNode = OrgNode from HumanResources.EmployeeDemo Where EmployeeID = @ManagerID  --Get the Max Child Select @MaxChild = Max(OrgNode) from HumanResources.EmployeeDemo Where OrgNode.GetAncestor(1) = @ManagerNode --Return the Value RETURN @MaxChild END

 

If you like the article then drop me a quick email. If want to know more then let me know and I would be happy to address your question. If you find a glitch in the article then let me know with your suggestion, I would be happy to correct.

I plan to bring in a few more posts on the new Date / Time data types and the exciting feature of FileStream in near future so keep a watch.

..End of this article

 

 

 

 

I have disabled comments on my blog because of the incoming spam but there are few interesting emails which I get and I think are really worth sharing with everyone because it will broaden the knowledge of the people reading this post. I am hiding the details of the sender.

 

Email Received

Great stuff, I'm implementing a social network and I really need this.  I wonder if your methods could be rewritten for sqlserver2005 using CDE's, then compare performance with sqlserver2008?  It would also help with development until 2008 ships.  Nervous about installing sqlserver2008 side by side with sqlserver2005 (especially beta). Thanks Again

 

My Reply

Yes, you can take the HierarchyID assembly shipped in Sql2008 and wrap a UDT around it and use it in SQL2005. You won't get some of the features (like translation of IsDescendant and IsAncestor queries to use depth and breadth first indices automatically).  You can also use the assembly only on the mid-tier/client and store the values as varbinary(900) in the database.