Cte Vs. T-sql Loop For Determining Depth Of Object Hierarchy

I have a table consisting of about 70,000 rows and two columns (both VARCHAR(16)): id and parent_id. I'd like to populate a 'depth' column that shows how far a particular record is

Solution 1:

You would need an index on parent_id. The recursive part of a CTE will always use a nested loops join and is impervious to join hints (Results are added to a stack spool and the rows are processed one by one in LIFO order)

Without an index on parent_id it will need to scan the table multiple times on the inner side of the nested loops. Performance will degrade exponentially with number of rows.

Your query without recursion will be able to use different join types (hash or merge) that only scan the table twice for each level of recursion. Most likely hash join in this case as you have no useful indexes that would avoid a sort.

Solution 2:

Have you considered using the HierarchyID datatype? It would make your life so much easier.

CREATETABLE Groups.tblHierarchyNode
(
        NodeID              IntIDENTITY (0,1),
        NodeHID             HierarchyID NOTNULL,   -- DB Hierarchy ID of where I am in a tree
        HierarchyLevel      AS NodeHID.GetLevel(),  -- Numerical level of where I am in tree
)

I use this for a lot of my hierarchical tables now. You have to be a bit smarter on table population, but reports are a breeze, as is moving up and down the hierarchy, getting ancestors, descendants and so on.