Re: Implementing trees in a relational database

In article <2uG19.35388$vB3.1938069_at_twister.southeast.rr.com>, "K. Brian Kelley" <rev_brian_at_nospam.hotmail.com> wrote:

> There's a new book from O'Reilly, Transact-SQL Cookbook, which has a
> chapter on hierarchies. It is presented in a Problem, Solution,
> Discussion format.
>
> --
> K. Brian Kelley
> Author: Start to Finish Guide to SQL Server Performance Monitoring
> (http://www.netimpress.com)
> SQL Server Central Columnist
> http://www.sqlservercentral.com/columnists/bkelley/ --
>
> "Jeff Clausius" <jeffc_at_sourcegear.nospam.com> wrote in message
>
>> "advanced transact-sql for sql server 2000" by itzik ben-gan and tom
>> moreau - whole chapter dedicated to modeling a hierarchical structure
>> in sql server.
>>

I glanced through both of the above recently, and I've forgotten which method the first uses, but the second one uses the materialized path method, which has been mentioned a number of times in c.d.theory and elsewhere. One tip that I didn't see in the book is to use varbinary columns instead of converting integer ID's to varchar to represent the path. Concatenating 4-byte integer ID's to form a varbinary path is a good space-saver, and the ordering still works lexicographically.

I have a few tree-related scripts at http://willets.org/sqlgraphs.html. I haven't tried to cover all the different methods yet, but I may add more. Received on Wed Jul 31 2002 - 23:05:16 CEST