Sql2008 HierarchyID

>> The new HierarchyID data type, will this data type automatically prevent the possibility of endless recursion? That is definitely the biggest headache of trying to model tree-data currently with (parent_node_id, child_node_id) fields [sic:columns]. <<

I think you meant "prevent cycles" (structure concept) instead of
"endless recursion" (procedural concept). The answer as I understand
it, Nope. The HierarchyID is a proprietary version of path
enumeration, and the constraint for preventing cycles in that model is
pretty ugly. You have to check all the path strings for duplicate
node_ids. The only way I know to do this anywhere close to fast, is
to replace every node_id with an empty string in every path string to
see if it will change the length of the path by more than one
node_id.
 >> Stay informed about: Sql2008 HierarchyID 

A column of data type HIERARCHYID does not represent a tree or graph
automatically. It is up to you to assign values that represent the desired
relationship between nodes.

The HIERARCHYID data type does not prevent cyclic paths by itself. You can
easily simulate one:

CREATE TABLE Employees (
emp_id INT NOT NULL,
manager_id INT,
org_chart_path HIERARCHYID);

INSERT INTO Employees
VALUES (1, NULL, '/1/'),
(2, 1, '/1/2/'),
(3, 2, '/1/2/3/'),
(1, 3, '/1/2/3/1/');

You have to handle it via constraints. In the example below the PRIMARY KEY
will not allow cyclic occurrences:

CREATE TABLE Employees (
emp_id INT NOT NULL PRIMARY KEY,
manager_id INT REFERENCES Employees(emp_id),
org_chart_path HIERARCHYID);

This way the HIERARCHYID data type can be used to address problems that
involve cyclic graphs as well as tree structures.

HTH,

Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: Sql2008 HierarchyID 

Alex,

The HierarchyID is actually implemented as materialized path. In the example
I posted you could easily add a CHECK constraint:

CHECK (org_chart_path.ToString() NOT LIKE '%/' + CAST(emp_id AS VARCHAR(10))
+ '/_%')

That will restrict the path to prevent cycles.

Plamen Ratchev
http://www.SQLStudio.com
 >> Stay informed about: Sql2008 HierarchyID