Just sharing some of my inconsequential lunch conversations with you... RSS  

Sunday, October 21, 2007

SQL Server 2008: hierarchyid Data Types

Relational databases have matured to such a level that doesn't leave much room for improvement. Here's one of the rare exceptions: hierarchyid Data Types.

Use hierarchyid (Transact-SQL) as a data type to create tables with a hierarchical structure, or to reference the hierarchical structure of data in another location. Use Hierarchical Methods (SQL Server) to query and perform work with hierarchical data by using Transact-SQL.

Hierarchical data is defined as a set of data items that are related to each other by hierarchical relationships. Hierarchical relationships are where one item of data is the parent of another item. Hierarchical data is common in databases. Examples include the following:

  • An organizational structure
  • A file system
  • A set of tasks in a project
  • A taxonomy of language terms
  • A graph of links between Web pages
Here's a sample:

SELECT * FROM Employees WHERE EmployeeId.GetAncestor(1) = @value

1 comment:

Koala said...

This "feature" already exists in Oracle a long time ago. They call it materialized views. Ok, they are dificult to design and hard to mantain, but the concept is there.
Whit one bonus, if you query a join that results in a subset of this view or even a supra-set of it, oracle is smart enough to do some query-rewrite and use this view instead of an heavy join.

Someone-who-is-now-on-the-other-side-of-barricade

Development Catharsis :: Copyright 2006 Mário Romano