SQL Server 2019中STRING_SPLIT结合ROW_NUMBER()能否保证顺序确定性？

核心结论 #

使用order by (select null)搭配STRING_SPLIT时，返回的顺序完全不具备确定性，无法保证和原始字符串的拆分顺序一致。

原因说明 #

SQL Server官方文档明确指出：在未启用ordinal参数（仅SQL Server 2022及更高版本支持）的情况下，STRING_SPLIT返回的行顺序是不固定的，数据库优化器可能根据执行计划（如并行处理、存储引擎的读取顺序等）任意调整输出顺序。而order by (select null)只是为ROW_NUMBER()提供一个合法的排序占位符，它不会强制数据库遵循原始字符串的拆分顺序——每次执行查询都可能得到不同的seq值，完全无法满足你保留原始顺序的需求。

可行替代方案（SQL Server 2019适用） #

方案1：递归CTE拆分 #

通过递归公共表表达式（CTE）逐段拆分字符串，严格保留原始顺序：

DECLARE @CurrentPath NVARCHAR(MAX) = 'your/path/here';

WITH SplitCTE AS (
    SELECT
        1 AS seq,
        CAST('' AS NVARCHAR(MAX)) AS part,
        @CurrentPath + '/' AS remaining
    UNION ALL
    SELECT
        seq + 1,
        CAST(LEFT(remaining, CHARINDEX('/', remaining) - 1) AS NVARCHAR(MAX)),
        CAST(SUBSTRING(remaining, CHARINDEX('/', remaining) + 1, LEN(remaining)) AS NVARCHAR(MAX))
    FROM SplitCTE
    WHERE CHARINDEX('/', remaining) > 0
)
SELECT seq, part
FROM SplitCTE
WHERE part <> ''
OPTION (MAXRECURSION 0); -- 路径过长时需开启此选项，避免递归次数限制

方案2：自定义表值函数 #

创建一个返回带序号的拆分结果的表值函数，复用性更强：

CREATE FUNCTION dbo.SplitStringWithOrdinal(
    @InputString NVARCHAR(MAX),
    @Delimiter NVARCHAR(10)
)
RETURNS @Result TABLE (seq INT, value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT = 1;
    DECLARE @EndIndex INT;
    DECLARE @Seq INT = 1;

    WHILE CHARINDEX(@Delimiter, @InputString, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString, @StartIndex);
        INSERT INTO @Result (seq, value)
        VALUES (@Seq, SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex));
        SET @StartIndex = @EndIndex + LEN(@Delimiter);
        SET @Seq = @Seq + 1;
    END

    -- 插入最后一段非空内容
    IF SUBSTRING(@InputString, @StartIndex, LEN(@InputString) - @StartIndex + 1) <> ''
    BEGIN
        INSERT INTO @Result (seq, value)
        VALUES (@Seq, SUBSTRING(@InputString, @StartIndex, LEN(@InputString) - @StartIndex + 1));
    END

    RETURN;
END;

-- 使用示例
SELECT * FROM dbo.SplitStringWithOrdinal('your/path/here', '/');

这两种方法都能确保拆分后的结果严格遵循原始字符串中各部分的出现顺序，完全满足你的需求。

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