如何高效缓存/索引含CTE的非确定性函数生成的层级数据？

我需要一套高性能的缓存/索引方案，用于处理无法通过持久化计算列、标准索引或索引视图实现索引的数据——这类数据的逻辑涉及CTE或被SQL Server判定为非确定性的函数。

核心场景是层级结构：层级中较低的后代节点需要持久化其上层祖先节点的特定值。当祖先节点的属性发生变更时，所有关联后代节点的对应值必须同步更新；同时要支持基于这些持久化关联值的高效查询（例如查找拥有同一指定类型祖先的所有节点）。

原方案在小数据集上运行正常，但在千万级规模的数据集上性能明显下降，现寻求一种基于变更触发持久化的方案，最好能模仿SQL Server管理索引的机制，自动判断数据过期并完成更新。

示例表结构与测试数据 #

DROP TABLE IF EXISTS [dbo].[TESTSTRUCTURES];

CREATE TABLE [dbo].[TESTSTRUCTURES](
    [CODE] nvarchar(10) NOT NULL,               -- 层级的唯一编码
    [PARENT] nvarchar(10) NULL,                 -- 父层级的编码
    [TYPE] nvarchar(10) NOT NULL,               -- 层级类型
    [TYPEDESC] nvarchar(30) NOT NULL,           -- 类型描述
    CONSTRAINT [PRIK_TESTSTRUCTURES] PRIMARY KEY CLUSTERED  
    (
        [CODE] ASC
    )
    WITH ( IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) 
    ON [PRIMARY]
    ) 
ON [PRIMARY];
GO

INSERT INTO [dbo].[TESTSTRUCTURES]
( [CODE], [PARENT], [TYPE], [TYPEDESC] )
VALUES 
( N'10001', Null, N'Level 1', N'I am a tree' ),
( N'10002', N'10001', N'Level 2', N'I prefer to swim' ),
( N'10003', N'10002', N'Level 3', N'Apples taste like gummy bears' ),
( N'10004', N'10003', N'Level 4', N'I enjoy hiking' ),
( N'10005', N'10004', N'Level 5', N'I am a stone' );
GO

获取祖先特征的现有函数 #

CREATE OR ALTER FUNCTION [dbo].[GETSTRUCTUREBYTYPE]
(
    @Code nvarchar(10), -- 锚定节点的编码
    @Type nvarchar(10)  -- 要查找的祖先类型
)
RETURNS TABLE
AS
RETURN 
    WITH CTE ( [CODE], [PARENT], [TYPE], [TYPEDESC], [DEPTH] ) AS
    (
        SELECT [CODE], [PARENT], [TYPE], [TYPEDESC], 1 AS [DEPTH]
        FROM [dbo].[TESTSTRUCTURES]
        WHERE [CODE] = @Code

        UNION ALL

        SELECT [STRUCT].[CODE], [STRUCT].[PARENT], [STRUCT].[TYPE], [STRUCT].[TYPEDESC], CTE.[DEPTH] + 1
        FROM [dbo].[TESTSTRUCTURES] [STRUCT]
        INNER JOIN CTE ON CTE.[PARENT] = [STRUCT].[CODE]
        WHERE CTE.[TYPE] != @Type
    )
    SELECT *
    FROM CTE
    WHERE [TYPE] = @Type;
GO

注：尽管该函数的逻辑是确定性的，但由于使用了CTE，SQL Server将其判定为非确定性函数，无法用于持久化计算列或索引视图。

内容的提问来源于stack exchange，提问作者Storm