SQL Server 2022 URI拆分计算列报错及多段查询优化方案问询

报错原因 #

你遇到的错误是因为持久化计算列不允许使用子查询，仅支持标量表达式。STRING_SPLIT配合子查询的写法不符合要求，因此需要改用其他方式提取URI分段。

可行方案 #

方案1：使用内置字符串函数组合提取分段（无额外函数）

通过CHARINDEX和SUBSTRING的嵌套组合，直接在计算列表达式中提取指定分段，完全使用内置标量函数，满足计算列的要求。

以提取第3段为例，创建持久化计算列的语句如下：

ALTER TABLE MyTable 
ADD SpotName AS (
    CAST(
        SUBSTRING(
            FullUri,
            -- 定位第3段的起始位置：跳过前两个斜杠后的位置
            CHARINDEX('/', FullUri, CHARINDEX('/', FullUri, 1) + 1) + 1,
            -- 计算第3段的长度：找到第3个斜杠的位置，减去起始位置
            CASE 
                WHEN CHARINDEX('/', FullUri, CHARINDEX('/', FullUri, CHARINDEX('/', FullUri, 1) + 1) + 1) = 0 
                THEN LEN(FullUri) + 1 
                ELSE CHARINDEX('/', FullUri, CHARINDEX('/', FullUri, CHARINDEX('/', FullUri, 1) + 1) + 1) 
            END 
            - CHARINDEX('/', FullUri, CHARINDEX('/', FullUri, 1) + 1) - 1
        ) AS VARCHAR(128)
    )
) PERSISTED

如果需要提取其他分段（比如第1、2、4、5段），只需调整CHARINDEX的嵌套层数即可。例如提取第1段的表达式为：

CAST(SUBSTRING(FullUri, 1, CHARINDEX('/', FullUri, 1) - 1) AS VARCHAR(128))

创建索引的语句：

CREATE NONCLUSTERED INDEX IX_MyTable_SpotName ON MyTable(SpotName)

优点：无需额外依赖，性能较好；缺点：分段数越多，表达式越冗长，维护成本高。

方案2：使用确定性标量UDF提取分段（可复用）

创建一个带SCHEMABINDING的确定性标量函数，封装URI分段提取逻辑，然后在计算列中调用该函数。

1. 创建函数：

CREATE FUNCTION dbo.GetUriSegment(@Uri VARCHAR(MAX), @SegmentNum INT)
RETURNS VARCHAR(128)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @StartPos INT = 1, @EndPos INT, @CurrentSegment INT = 0

    WHILE @CurrentSegment < @SegmentNum
    BEGIN
        -- 定位下一个斜杠的位置
        SET @StartPos = CHARINDEX('/', @Uri, @StartPos) + 1
        -- 如果没有找到足够的斜杠，返回NULL
        IF @StartPos = 1 RETURN NULL
        SET @CurrentSegment += 1
    END

    -- 定位当前分段的结束位置（下一个斜杠或字符串末尾）
    SET @EndPos = CHARINDEX('/', @Uri, @StartPos)
    IF @EndPos = 0 SET @EndPos = LEN(@Uri) + 1

    RETURN SUBSTRING(@Uri, @StartPos, @EndPos - @StartPos)
END

2. 创建持久化计算列：

-- 提取第3段
ALTER TABLE MyTable ADD SpotName AS dbo.GetUriSegment(FullUri, 3) PERSISTED
-- 提取第1段
ALTER TABLE MyTable ADD Segment1 AS dbo.GetUriSegment(FullUri, 1) PERSISTED
-- 同理创建其他分段的计算列

3. 创建索引：

CREATE NONCLUSTERED INDEX IX_MyTable_Segment1 ON MyTable(Segment1)
CREATE NONCLUSTERED INDEX IX_MyTable_SpotName ON MyTable(SpotName)
-- 其他分段索引同理

优点：代码复用性强，逻辑清晰；缺点：标量函数本身有一定性能开销，但由于是持久化计算列，数据预存储，查询时无额外计算，影响可忽略。

方案3：预处理分段列（最高性能）

直接在表中新增5个分段列（如Segment1至Segment5），通过触发器或应用层逻辑在数据插入/更新时直接写入分段值，然后对这些列建索引。

1. 新增列：

ALTER TABLE MyTable ADD 
    Segment1 VARCHAR(128),
    Segment2 VARCHAR(128),
    Segment3 VARCHAR(128),
    Segment4 VARCHAR(128),
    Segment5 VARCHAR(128)

2. 创建触发器维护分段值：

CREATE TRIGGER trg_MyTable_UpdateUriSegments
ON MyTable
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
    SET
        Segment1 = dbo.GetUriSegment(i.FullUri, 1),
        Segment2 = dbo.GetUriSegment(i.FullUri, 2),
        Segment3 = dbo.GetUriSegment(i.FullUri, 3),
        Segment4 = dbo.GetUriSegment(i.FullUri, 4),
        Segment5 = dbo.GetUriSegment(i.FullUri, 5)
    FROM MyTable t
    JOIN inserted i ON t.Id = i.Id -- 假设表有主键Id
END

3. 创建索引：

CREATE NONCLUSTERED INDEX IX_MyTable_Segment1 ON MyTable(Segment1)
CREATE NONCLUSTERED INDEX IX_MyTable_Segment2 ON MyTable(Segment2)
-- 其他分段索引同理

优点：查询性能最优，直接读取预存数据；缺点：需要维护触发器或修改应用层逻辑，增加了系统复杂度。

方案选择建议 #

• 如果仅需提取少量分段（如2-3个），优先选择方案1；
• 如果需要提取多个分段且追求代码可维护性，选择方案2；
• 如果对查询性能要求极高，且数据写入频率低于查询频率，选择方案3。

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