How to store a tree in SQL database

sql sql-server tsql hierarchical-data
kalan picture kalan · Jul 9, 2010 · Viewed 15.2k times · Source

I have to store a tree in a database, so what is the best way to do this? Show the method you use and name its pros and cons. (I am using SQL Server 2005)

Answer

SWeko picture SWeko · Jul 9, 2010

I found the discussion in the SQL Anti-patterns very helpfull, as it also focuses on the drawbacks of every implementation.

Also, The slides 48-77 in this presentation reiterate that analisys.

Bottom line, there is no such thing as a generic tree, and no silver bullet for SQL trees. You'll have to ask yourself about the data, how and how much will they be selected, modified, will branches be moved around, etc, and based on those answers implement a suitable solution.

Related questions

SQL query for parent-child chain

I have a single table that can refer to one other member in the table as a parent. That parent could also refer to one other row as its parent...and so on. id col1 col2 parentID 1 foo bar NULL 2 …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More