Tree structure data query in SQL Server

Question

I have a table Person that has 3 columns: Id, Name, ParentId where ParentId is the Id of the parent row.

Currently, to display the entire tree, it would have to loop through all child elements until there's no more child elements. It doesn't seem too efficient.

Is there a better and more efficient way to query this data?

Also, is there a better way to represent this tree like structure in a SQL Server database? An alternative design for my table/database?

See http://stackoverflow.com/questions/935098/database-structure-for-tree-data-structure for similar question with answers. — JeremyDWill, Apr 29 '12 at 04:32
See Bill Karwin's [SQL Antipatterns strike back](http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back) slide deck - he describes several antipatterns - amongst them the "naive tree" that you have - and offers possible solutions for it. He also has a great book by the same name [SQL Antipatterns](http://pragprog.com/book/bksqla/sql-antipatterns) - highly recommended! — marc_s, Apr 29 '12 at 09:17

score 36 · Accepted Answer · answered Apr 29 '12 at 04:28

I don't think there's anything wrong with the design, assuming you have a limited level of parent-child relationships. Here is a quick example of retrieving the relationship using a recursive CTE:

USE tempdb;
GO

CREATE TABLE dbo.tree
(
    ID INT PRIMARY KEY,
    name VARCHAR(32),
    ParentID INT FOREIGN KEY REFERENCES dbo.tree(ID)
);

INSERT dbo.tree SELECT 1, 'grandpa', NULL
UNION ALL SELECT 2, 'dad', 1
UNION ALL SELECT 3, 'me', 2
UNION ALL SELECT 4, 'mom', 1
UNION ALL SELECT 5, 'grandma', NULL;

;WITH x AS
(
    -- anchor:
    SELECT ID, name, ParentID, [level] = 0
    FROM dbo.tree WHERE ParentID IS NULL
    UNION ALL
    -- recursive:
    SELECT t.ID, t.name, t.ParentID, [level] = x.[level] + 1
    FROM x INNER JOIN dbo.tree AS t
    ON t.ParentID = x.ID
)
SELECT ID, name, ParentID, [level] FROM x
ORDER BY [level]
OPTION (MAXRECURSION 32);
GO

Don't forget to clean up:

DROP TABLE dbo.tree;

This might be a useful article. An alternative is hierarchyid but I find it overly complex for most scenarios.

It's not relevant to the topic, but aren't your 'mom' and 'dad' siblings, according to the structure of your tree? :) I mean a daily live meaning, like they have the same father. Oh, never mind — Varvara Kalinina, Jun 07 '16 at 08:58

score 8 · Answer 2 · answered Apr 29 '12 at 10:25

Aaron Bertrands answer is very good for the general case. If you only ever need to display the whole tree at once, you can just query the whole table and perform the tree-building in-memory. This is likely to be more convenient and flexible. Performance also will be slightly better (the whole table needs to be downloaded anyway and C# is faster for such calculations than SQL Server).

If you only need a part of the tree this method is not recommended because you'd be downloading more data than needed.

Tree structure data query in SQL Server

2 Answers2

Linked