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
)
go
insert into tblOrgStructure — root node
values
(
hierarchyid::GetRoot(), — orgnode
— orglevel (will be computed)
111, — emp_id
‘Allen Anderson’, — name
‘President’ — title
)
insert into tblOrgStructure values — child of root
(
hierarchyid::GetRoot().GetDescendant(null,null),
222,
‘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
(
@managersOrgnode.GetDescendant(@newOrgnode,null),
333,
‘Chris Collins’,
‘Director’
)
 
select orgnode.ToString(),
orglevel,
emp_id,
emp_name,
emp_title
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. 
Advertisements
This entry was posted in Software Test Automation. Bookmark the permalink.