0

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

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

gulbaek
  • 2,481
  • 13
  • 44
  • 65
  • Why does this need to be created in SQL server? Wouldn't a design based on a naturally hierarchical data repository such as XML fit better? – RB. Jan 05 '11 at 19:41
  • Well a xml file would be a way to do it, but how is the select performance of a xml file compared to select in a sql server? – gulbaek Jan 06 '11 at 09:02
  • Sounds like a perfect case for hierachyid. – usr May 20 '12 at 22:59

2 Answers2

1

SQL Server support recursive CTEs, 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.

vyegorov
  • 21,787
  • 7
  • 59
  • 73
0

Maybe take a look at nested sets? See Joe Celko.

dandcg
  • 442
  • 4
  • 16