I began looking today at new features planned for SQL Server 2008. One that caught my eye is new support for tree-structured data in the form of a new, built-in data type called hierarchyid that allows representation of parent/child relationships without the need for recursive, foreign keys. For example, rather than use the following, common approach to representing the manager/employee relationship:

create table employee (
   EmployeeID int,
   ManagerID int,

   ...other columns...

   constraint emp_has_mgr 
      foreign key (ManagerID)
      references employee
) 

You can dispense with the separate manager ID column and its associated foreign-key constraint, and encapsulate the organizational structure into just one column of type hierarchyid:

create table employee (
   OrgNode hierarchyid,
   EmployeeID int,

   ...other columns...

)

Simple! Elegant. No foreign key constraint to bother with. A single column defines the tree. And the hierarchyid data type includes a number of useful methods for manipulating and querying tree-structured data. There are benefits to indexing as well -- you can index to optimize for either breadth-first or depth-first searching.

Following is a link to a page giving an overview of the new type, that explains some of the pros and cons of using it:

http://msdn2.microsoft.com/en-us/library/bb677173(SQL.100).aspx

And following is a link to a tutorial on the new type:

http://msdn2.microsoft.com/en-us/library/bb677213(SQL.100).aspx

If you work with tree-structured data now, then definitely read up on the new data type and learn what it has to offer. Even if you don't currently manage hierarchical data, at least follow the first link above to get an idea of the support available in SQL Server 2008. Tree-structured data is common enough that anyone working with SQL Server should have at least a passing knowledge of the hierarchyid type and how to use it.