MS SQL Sever 2000 - Design question part II - tree table using materialized path OR nested sets

Database:  MS SQL 2000 - No CTE Support

This is part II of another question.  I'm designing a db table that will store a virtual folder tree. Like a table based windows explorer.  The table will be used in a web application, so performance is important.

I've done a fair amount of reading but I'm still in the "theory" stage, trying to decide on a model :)  So I'm looking for some general feedback/experiences/issues from others, that have used one or more of the models below in a production environment, to help with the decision making proccess.


- NESTED SETS  
- MATERIALIZED PATH  (1.2.10, 1.2.11, 1.2.12, ...)
- Or a combination of:
     ADJACENCY (parent + child) and NESTED SETS
     ADJACENCY (parent + child) and MATERIALIZED PATH

I'm hoping for some general feedback on the following:

1) Which of these models have you used and what was your experience in terms of  
   -  performance  
   -  maintenance
   -  what volume of data was involved?

2) With nested sets, node changes require a complete table update. It seems like this would provide poor performance for medium/large tables or cause blocking problems.  Can anyone confirm or refute this?

3) Materialized path seems like a good solution, but it requires more storage and the field may not be indexable? It would also require LIKE '1.2.%' statements to return entire branches. Would that be a performance killer with medium/large tables?

Again, since I've already done some searching and reading, I would prefer comments from those who have actually used one of these models in a production environment :)

I'll check for any responses later.

TIAStart Free Trial