使用 ORDER BY 進行字元串連接時結果不正確

我試圖理解為什麼在使用order by. 有一系列組合有效並為我們提供了正確的數據。一件奇怪的事情是，當一個唯一的非聚集索引被添加到表中時，它會產生一個不正確的結果。

下面的腳本將重現不正確的結果。我還添加了 5 個測試案例，我認為這很奇怪。

CREATE DATABASE [Stringer];

DROP TABLE IF EXISTS dbo.Options

CREATE TABLE dbo.Options (OptionId int NOT NULL,Keyword nvarchar(8) NOT NULL, OptionPartOrder int NULL, OptionRank int NOT NULL, OptionCategory nvarchar(50) NOT NULL);

INSERT INTO dbo.Options VALUES (1000000,N'Socks' , NULL, 1, N'Size'), (5000000, N'Socks', NULL, 2, N'Colour');

ALTER TABLE [dbo].[Options] DROP CONSTRAINT [uq_OptionId];
ALTER TABLE [dbo].[Options] ADD CONSTRAINT [uq_OptionId] PRIMARY KEY NONCLUSTERED ([OptionId] ASC);
CREATE CLUSTERED INDEX [cx_keyword] ON [dbo].[Options]([keyword] ASC);
DROP INDEX [cx_keyword] ON [dbo].[Options];

CREATE OR ALTER FUNCTION [dbo].[Split_dan] (@list nvarchar(MAX), @spliton nvarchar(5)) RETURNS TABLE
WITH SCHEMABINDING
AS
   RETURN (   SELECT   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id
                     , CA.v AS [value]
              FROM STRING_SPLIT(@list, @spliton) s
              CROSS APPLY( VALUES(RTRIM(LTRIM(s.value))) ) AS CA(v)
              );

查詢：

USE [Stringer];
GO

DECLARE
   @keyword NVARCHAR(8)= N'Socks',
   @optionids NVARCHAR(250)= N'1000000,5000000',
   @delimit_in NVARCHAR(5)=',',
   @delimit_out NVARCHAR(5)=',',
   @optionidout NVARCHAR(max);

SELECT @optionidout = ISNULL(@optionidout, '') + s.[value] + @delimit_out --, OptionId, OptionPartOrder, OptionRank, OptionCategory
FROM dbo.Options o
INNER JOIN dbo.Split_dan(@optionids, @delimit_in) s ON o.[OptionId] = CAST(s.[value] AS int) AND ISNUMERIC(s.[value]) = 1
WHERE o.[Keyword] = @keyword
ORDER BY ISNULL(o.OptionPartOrder,0), ISNULL(o.OptionRank, -1) , o.OptionCategory;


IF CHARINDEX(@delimit_out, @optionidout) > 0
       SET @optionidout = LEFT(@optionidout, LEN(@optionidout) - LEN(@delimit_out))

SELECT @optionidout AS 'options';

/* -- TEST CASE --
- heap table or clustered (without unique nonclustered) produces correct result.
- adding unique nonclustered index (with/without clustering key) produces incorrect result
- with unique nonclustered index, removing ISNULL function on o.OptionPartOrder in ORDER BY produces correct result.
- with unique nonclustered index, changing the s.value on the SELECT list to CAST(o.[OptionId] AS nvarchar) produces correct result.
- with unique nonclustered index, removing the ISNULL(o.OptionPartOrder,0) expression in ORDER BY clause produces correct result
*/

• 測試案例 1 結果：1000000,5000000
• 測試案例 2 結果：5000000
• 測試案例 3 結果：1000000,5000000
• 測試案例 4 結果：1000000,5000000
• 測試案例 5 結果：1000000,5000000

我很好奇**“為什麼”**會發生這種情況？這可能是 SQL Server 中設計的限制設計或錯誤嗎？

我已經在 SQL Server 2016 Standard Edition 和 SQL Server 2019 Developer Edition 上執行了腳本，並且都產生了相同的結果。

正如 Dan 在評論中指出的那樣，長期以來，這一直是一個“錯誤”，實際上現在在 Microsoft 文件中聲明它是設計使然，因為分配是以非確定性的方式處理的。

您可以在SELECT @local_variable - Antipattern use of recursive variable assignment中找到有關 Microsoft 將其稱為反模式以及從結果集中為變數分配串聯值字元串的推薦方法的更多資訊：

SELECT @Var = <expression containing @Var>
FROM
...

在這種情況下，不能保證@Var會逐行更新。例如，@Var可以@Var為所有行設置為初始值。這是因為處理分配的順序和頻率是不確定的。這適用於包含變數字元串連接的表達式，如下所示，但也適用於具有非字元串變數或 += 樣式運算符的表達式。使用聚合函式代替基於集合的操作而不是逐行操作。

對於字元串連接，請考慮STRING_AGGSQL Server 2017 中引入的函式…

再次按照 Dan 的說法，這是他寫的 DBA.StackExchange 答案，其中詳細說明了正確遞歸設置變數的不同解決方案。Microsoft推薦的STRING_AGG()解決方案僅適用於 SQL Server 2017 及更高版本。或者，有一個使用FOR XML舊版本 SQL Server 中可用的解決方案。

引用自：https://dba.stackexchange.com/questions/314954