SQL Server 2008 and Testing – HierarchyID

SQL Server 2008, due to be released within a few months, has a ton of new features. I’ve been looking at RC0 (release candidate 0), trying to determine the implications SQL Server 2008 has for testing. The first step in such an analysis is to nderstand exactly what the new features of SQL Server 2008 are. One of the interesting new features is a new HierarchyID data type. You can use HierarchyID to represent hierarchical data, such as the manager and report-to relationships in a company. Of course  you can represent such relationships but HierarchyID provides a de facto standard. I didn’t find any good HierarchyID examples (by that I mean examples which expose aspects of HierarchyID that are relevant to testing implications) so I experimented. Consider this example based on one in the documentation:
— assume we have an existing table of employees
create table tblOrgStructure
orgnode hierarchyid primary key clustered,
orglevel as orgnode.GetLevel(), — computed column
emp_id int unique not null,
emp_name varchar(50) not null,
emp_title varchar(50) null
insert into tblOrgStructure — root node
hierarchyid::GetRoot(), — orgnode
— orglevel (will be computed)
111, — emp_id
‘Allen Anderson’, — name
‘President’ — title
insert into tblOrgStructure values — child of root
‘Bob Baker’,
‘Vice President’
— child of non-root
declare @managersOrgnode hierarchyid
declare @newOrgnode hierarchyid
— 1. use manager’s ID to get manager’s orgnode
select @managersOrgnode = orgnode from tblOrgStructure where emp_id = 222
— 2. use manager’s orgnode to get insert position
select @newOrgnode = MAX(orgnode) from tblOrgStructure where orgnode.GetAncestor(1) = @managersOrgnode
— 3. insert new node
insert into tblOrgStructure values
‘Chris Collins’,
select orgnode.ToString(),
from tblOrgStructure
As you can see, the trick is to determine the orgnode which is a HierarchyID type. So, what does this mean for testing? First, we’ll assume that the SQL Server 2008 folks have tested the heck out of HierarchyID and its associated functions such as GetDescendant(). So, by testing HierarchyID, I really mean testing an application program (or perhaps a library module of some sort) which uses a SQL table which has one or more HierarchyID columns. And this means you’d have use standard hierarchical data testing techniques — examine empty organization structures, org structures with just one node, and so on. 
This entry was posted in Software Test Automation. Bookmark the permalink.