主页 / user-214884

Dan Co's questions

Martin Hope
Dan Co
Asked: 2022-07-28 19:08:38 +0800 CST
  • 3

我试图理解为什么在使用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 上运行了脚本，并且都产生了相同的结果。

sql-server sql-server-2016