sql server - How to store directory / hierarchy / tree structure in the database?

Question

How do i store a directory / hierarchy / tree structure in the database? Namely MSSQL Server.

@olavk: Doesn't look like you've seen my own answer. The way i use is way better than recursive queries :)

p.p.s. This is the way to go!

Answer 1

There are many ways to store hierarchies in SQL databases. Which one to choose depends on which DBMS product you use, and how the data will be used. As you have used the MSSQL2005 tag, I think you should start considering the "Adjacency List" model; if you find that it doesn't perform well for your application, then have a look at Vadim Tropashko's comparison which highlights differences between models with a focus on multiple performance characteristics.