different ways to handle a tree hierarchies in sql server...
since sql server doesn't have a build in type, or support, for nested / tree hierarchies, you need to borrow concepts more common in C data structures programming. You'll have to build custom schemas and procedures to handle this.
the simple approach is to use a nested key relationship. you can use this method along with recursion if your data is not complicated, since there is a limit. a stored procedure can only nest itself up to a maximum of 32 levels. if you exceed this limit, you will receive the following error:
Server: Msg 217, Level 16, State 1, Procedure sdss, Line 1
Maximum stored procedure nesting level exceeded (limit 32).
here are two approaches which get around this, using variations of an in-order/preorder tree traversal to sort the data.
naturally, there are advantages and disadvantages to either approach.
here are some links...
nested set model :
http://www.intelligententerprise.com/001020/celko1_2.jhtml
http://www.winnetmag.com/SQLServer/Article/ArticleID/8826/8826.html
indexed tree:
http://www.dbmsmag.com/9603d06.html
http://www.dbmsmag.com/9604d06.html
http://www.dbmsmag.com/9605d06.html
non-recursive nested key traversal :
http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915
node/map table:
http://www.sqlteam.com/item.asp?ItemID=8866