在SQL Server中查找顶级父级的最有效方法？

Question

给出下表

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

给定一个catID，我需要找到其顶级父级（parentID = 0）。

每天执行50-100次此查询。当前有100-200行（将来可能会更多）。多达8个层次的深度。我在考虑三种选择：

1. 使用递归方法
2. 创建一个视图
3. 添加另一列topParentID（最不满意）

哪个效率最高？

Answer 1

SQL2008 +：

为了存储层次结构，SQL Server包含HIERARCHYID数据类型。可以将上述数据“转换为”以使用HIERARCHYID“值”：

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/

转换后，我将删除parentID列。

HIERARCHYID是SQLCLR系统数据类型，其中包括以下方法：

• HidValue。GetLevel（）
• HidValue。GetAncestor（level）。

为了获得父节点，我将使用以下方法：

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/

此外，我将在hierarchyNode列上创建索引，因此：

CREATE UNIQUE INDEX IUN_Table_hierarchyNode
ON dbo.Table(hierarchyNode)

最后的查询将是：

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