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?

Answer 1

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.

Answer 2

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.