SQL Server 2008兼容模式下逗号分隔行转索引视图的技术问询

我们系统采用逗号分隔字符串存储关联ID的设计，初期数据量小时无性能问题，但随着数据增长，拆分查询耗时达30-60秒——核心原因是需要逐行遍历字符串、匹配特定ID并调用拆分逻辑。我尝试用XML+CROSS APPLY的方式生成拆分后的数据（如下查询），但由于CROSS APPLY不符合索引视图的创建要求，无法将其转为索引视图来提速。同时我们处于SQL Server 2008兼容模式，无法使用STRING_SPLIT等新特性，修改业务代码成本过高。

原数据与目标格式 #

原数据格式 #

目标索引视图格式 #

原拆分查询（耗时超30秒） #

SELECT DISTINCT ProductId, 
                LTRIM(RTRIM(m.n.value('.[1]', 'varchar(10)'))) AS OtherProductId
FROM (
        SELECT ProductId,
             CAST('<XMLRoot><RowData>'
             + REPLACE(OtherProductIds, ',', '</RowData><RowData>')
             + '</RowData></XMLRoot>' AS XML) AS x
        FROM MySourceTable
     ) t
     CROSS APPLY x.nodes('/XMLRoot/RowData') m(n)

解决方案：利用数字辅助表+确定性字符串函数创建索引视图 #

SQL Server索引视图不支持CROSS APPLY、XML节点拆分这类非确定性操作，但可以通过数字辅助表结合SUBSTRING、CHARINDEX等确定性函数实现拆分，满足索引视图的创建要求。

1. 创建数字辅助表 #

先创建一个包含连续整数的表，用于定位逗号分隔字符串中的每个ID位置：

CREATE TABLE dbo.Numbers (Number INT PRIMARY KEY);

-- 插入1到1000的连续数字（可根据实际最大ID数量调整上限）
WITH N AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM N WHERE n < 1000
)
INSERT INTO dbo.Numbers(Number)
SELECT n FROM N OPTION (MAXRECURSION 0);

2. 创建带SCHEMABINDING的视图 #

使用确定性函数拆分字符串，同时添加SCHEMABINDING（索引视图的强制要求）：

CREATE VIEW dbo.vw_ProductRelatedIds
WITH SCHEMABINDING
AS
SELECT 
    t.ProductId,
    -- 提取并清理每个ID
    LTRIM(RTRIM(SUBSTRING(t.OtherProductId, s.StartPos, s.EndPos - s.StartPos))) AS OtherProductId
FROM dbo.MySourceTable t
JOIN (
    -- 计算每个ID的起始和结束位置
    SELECT 
        t_inner.ProductId,
        CASE WHEN n.Number = 1 THEN 1 ELSE CHARINDEX(',', t_inner.OtherProductId, n.Number) + 1 END AS StartPos,
        ISNULL(CHARINDEX(',', t_inner.OtherProductId, CHARINDEX(',', t_inner.OtherProductId, n.Number) + 1), LEN(t_inner.OtherProductId) + 1) AS EndPos
    FROM dbo.Numbers n
    CROSS JOIN dbo.MySourceTable t_inner
    -- 筛选出每个分隔符的位置（或字符串起始位置）
    WHERE n.Number <= LEN(t_inner.OtherProductId) 
        AND (n.Number = 1 OR SUBSTRING(t_inner.OtherProductId, n.Number - 1, 1) = ',')
) s ON t.ProductId = s.ProductId
-- 过滤空值（避免拆分出空字符串）
WHERE LTRIM(RTRIM(SUBSTRING(t.OtherProductId, s.StartPos, s.EndPos - s.StartPos))) <> '';
GO

3. 创建唯一聚集索引 #

索引视图必须包含唯一聚集索引，同时通过索引的唯一性自动去重（替代原查询的DISTINCT）：

CREATE UNIQUE CLUSTERED INDEX IX_vw_ProductRelatedIds 
ON dbo.vw_ProductRelatedIds(ProductId, OtherProductId);
GO

关键说明 #

• 确定性操作：所有用到的函数（SUBSTRING、CHARINDEX、LEN等）都是确定性的，符合SQL Server对索引视图的要求。
• 自动同步：原表MySourceTable的数据变更会自动同步到索引视图，无需手动维护。
• 性能提升：索引视图将拆分后的数据持久化并建立索引，后续关联查询可直接利用索引，大幅降低耗时。
• 数字范围调整：如果OtherProductId中最多包含超过1000个ID，需调整数字辅助表的插入上限。

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