Looking at the new HierarchyID datatype after a successful (?) installation of SQL 2008 CTP 6. Drill down capabilities are usually something associated with OLAP. With the HierarchyID data type, these tree/org chart capabilities fall further into the SQL realm.
At first glance, I notice some immediate quirks for me:
1. It's a CLR extension rather than an embedded data type. Most newer types are probably going to fall into this realm. Rather than cracking open the engine, MS is adding some new headers, a spoiler, or a bigger airflow. Or maybe some performance spark plugs. For stability and ease of maintenance, it's probably the right way to go. If it ain't broke...
2. The syntaxes are a bit complex and not really T-SQL friendly. ToString()? Looks like C# to me... Intellisense choked on it.
3. I couldn't see a way to edit the data in the table directly. It's probably there, just not intuitive to me. 0x48 is a value? Why not just edit the table with /0/1/2/3 instead of using functions to insert siblings?
4. Sticking with Parent-Child keys and Common Table Expressions is probably the way to go for now, for simple things like department-organization or chart of accounts. Haven't seen how HierarchID will work with Analysis Services, or how it will work with .NET controls like TreeView.
6. Lots of interesting possibilities, but with CLR there always is.
An article on the HierarchyID at SQL Server Central confirms some of my findings.
So, as every technology in IT, you should correctly evaluate your needs before choosing HierachyID. Here are some clues helping for your choice. Classic design would be prefer in those cases:
· If the size of key is big and you need to optimize storage; even if HierarchyID is a compact type, you will rapidly exceed 4bytes.
· If you query directly unique elements, primary key or unique index will be better.
· If you often move intermediate nodes ; hierarchy updates are slower with HierarchyID type
The HierarchyID Datatype in SQL Server 2008 - SQL Server Central
More info on hierarchyID at Ravi's blog & MSDN forums.
http://blogs.msdn.com/manisblog/archive/2007/08/17/sql-server-2008-hierarchyid.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2820184&SiteID=1
Now, it's simple to setup a parent-child relationship with 2 keys in a table. It's tough to maintain. This is where HierarchyID seems to shine.
Plus it requires less typing to get at the data. And it just looks cleaner than a CTE.
Declare @Manager HierarchyID
Select @Manager = Orgnode from HumanResources.EmployeeDemo
Where EmployeeID = 1
Select Orgnode.ToString(),* from HumanResources.EmployeeDemo
Where @Manager.IsDescendant(OrgNode)=1
Order By OrgNode
.NET, SQL, and SSAS are getting blurrier every day.
No comments:
Post a Comment