Question: Hi I want to create a tree structure in a sql database. I need to have the following properties.

* Fast Select
* Easy to Select with only a string like Cat2/Cat4 -> Returns Cat5 & Cat6
* Support of Virtual Categories
* Insert should be easy, but not important compared to a fast Select
* Delete same as Insert, easy would be nice, but again it’s more important to be able to Select fast
* Reorder is again not as important as Select

![alt text](https://i.stack.imgur.com/eEVl6.png)

I have looked at Materialized-Path and hierarchyId, but most examples use a sample like AdventureWorks, that’s just too complicated, for my need.

Just for the record I use Sql Server 2008 R2 and C# 4.0

Answer: 
SQL Server support [recursive CTEs](http://msdn.microsoft.com/en-us/library/ms175972.aspx), they can probably help you.

You can build a tree structure from your data with the following SQL:

```
WITH cat_tree AS (
  SELECT cat_id, cat_name, parent_id,
         0 AS level, CAST('0' AS varchar(90)) AS path
    FROM cats
   WHERE parent_id IS NULL
  UNION ALL
  SELECT c.cat_id, c.cat_name, c.parent_id,
      ct.level + 1,
      CAST(ct.path+'.'+CAST(
       (row_number() OVER (ORDER BY ct.cat_id)) AS varchar) AS varchar(90))
    FROM cats c
    JOIN cat_tree ct ON c.parent_id = ct.cat_id)
SELECT * FROM cat_tree
 ORDER BY path;

```

Take a look at the results on [SQL Fiddle](http://www.sqlfiddle.com/#!3/a6505/8).
Maybe take a look at nested sets? See Joe Celko.