database - 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.