The most efficient way to find the top level parent in SQL Server?

sql, sql-server

Solution

SQL2008+:

To store hierarchies , SQL Server includes HIERARCHYID data type. Above data can be "converted" to use `HIERARCHYID` "values" thus:

catName     catID     parentID  hierarchyNode
=============================================
vehicles    1         0         /1/
cars        2         1         /1/2/
sedans      3         2         /1/2/3/
animals     4         0         /4/
cows        5         4         /4/5/

After conversion, I would drop `parentID` column.

HIERARCHYID is SQLCLR system data type which include following methods:

- HidValue.GetLevel()

- HidValue.GetAncestor(level).

To get parent node I would use these methods thus:

DECLARE @node HIERARCHYID
SET     @node = '/1/2/3/'

SELECT  
    currentNodeLvl= @node.GetLevel(),                                 --> 3
    parentAsHID   = @node.GetAncestor(@node.GetLevel() - 1),          --> 0x58
    parentAsString= @node.GetAncestor(@node.GetLevel() - 1).ToString()--> /1/

More, I would create an index on `hierarchyNode` column thus:

CREATE UNIQUE INDEX IUN_Table_hierarchyNode
ON dbo.Table(hierarchyNode)

and final query will be:

SELECT ..., prt.catID AS parentID
FROM dbo.Table crt -- Curent node
LEFT/INNER JOIN -- It depends on hierarchyID nullability 
dbo.MyTable prt -- Parent node
ON @node.GetAncestor(crt.hierarchyID.GetLevel() - 1).ToString() = prt.hierarchyID

Problem

Given the following table ``` catName catID parentID ================================= vehicles 1 0 cars 2 1 sedans 3 2 animals 4 0 cows 5 4 ``` Given a `catID`, I need to find its top level parent (`parentID = 0`). This query is executed 50-100 times a day. There are currently 100-200 rows (maybe more in the future). Up to 8 levels deep. I'm thinking three alternatives: - Using a recursive method - Creating a view - Adding another column `topParentID` (least favorable) Which will be the most efficient?

Original source