sql-server - 聚集索引可以改变动态SQL语句的行为并返回不同的结果吗？

Question

这个问题已经有答案了:

已关闭 10 年前。

Possible Duplicate:
ORDER BY suddenly conflicting with VARCHAR concatenation in TSQL

最近，我发现在一张表中创建聚集索引，这个聚集索引改变了动态SQL语句的结果。当表有聚集索引时，该语句仅返回最后一个归档结果。从表中删除聚集索引或删除“ORDER BY fieldnumber”，将返回完整结果(15 个字段)。行为的变化是由聚集索引与替换调用以及示例语句中的 order by 和 k 声明为 1000 vs max 引起的。

聚集索引是否可以更改动态 SQL 语句行为并返回不同的结果或其他我不知道的内容？欢迎大家提出意见!

--创建测试表

USE [test] 

GO 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO 

CREATE TABLE [dbo].[test]( 
[companyid] [int] NOT NULL, 
[fieldName] [nvarchar](50) NOT NULL, 
[fieldnumber] [tinyint] NOT NULL, 
[Tagname] [nvarchar](15) NULL 
) ON [PRIMARY] 

GO 

--插入测试数据

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Employee Status', 1, N'<CHARACTER_1>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Admin Grouping', 2, N'<CHARACTER_2>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Department Code', 3, N'<CHARACTER_3>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Job Code', 4, N'<CHARACTER_4>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'FLSA - Exempt', 5, N'<CHARACTER_5>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Action Field 06', 6, N'<CHARACTER_6>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Action Field 07', 7, N'<CHARACTER_7>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Action Field 08', 8, N'<CHARACTER_8>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Action Field 09', 9, N'<CHARACTER_9>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Action Field 10', 10, N'<CHARACTER_10>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'ProcessLevel', 11, N'<CHARACTER_11>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Department Name', 12, N'<CHARACTER_12>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Job Title', 13, N'<CHARACTER_13>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Information Field 04', 14, N'<CHARACTER_14>') 

INSERT [dbo].[test] ([companyid], [fieldName], [fieldnumber], [Tagname]) VALUES (1, N'Information Field 05', 15, N'<CHARACTER_15>') 

go 

--测试脚本

declare @k nvarchar(1000) --–-or max 

set @k = '' 

SELECT @k = @k + REPLACE(REPLACE(TagName,'<',''),'>','') + ',' FROM test WITH (NOLOCK) 
WHERE CompanyID = 1 ORDER BY fieldnumber 


select @k as test_result_without_index 

go 

--创建一个聚集索引来测试上面的脚本

CREATE CLUSTERED INDEX [ix-test] ON [dbo].[test] 
( 
[CompanyID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

GO 

-- 使用 nvarchar(1000) 测试聚集索引的行为变化的测试脚本

declare @k nvarchar(1000) --–-or max 

set @k = '' 

SELECT @k = @k + REPLACE(REPLACE(TagName,'<',''),'>','') + ',' FROM test WITH (NOLOCK) 
WHERE CompanyID = 1 ORDER BY fieldnumber 


select @k as test_result_with_clustered_index_varchar1000_combine 

go 

-- 使用 nvarchar(max) 测试行为在 nvarchar(max) 下没有变化的测试脚本 与聚集索引结合

declare @k nvarchar(Max) --–-or max 

set @k = '' 

SELECT @k = @k + REPLACE(REPLACE(TagName,'<',''),'>','') + ',' FROM test WITH (NOLOCK) 
WHERE CompanyID = 1 ORDER BY fieldnumber 


select @k as test_result_with_clustered_index_and_varcharMax__combine 

go 

--删除聚集索引

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[test]') AND name = N'ix-test') 

DROP INDEX [ix-test] ON [dbo].[test] WITH ( ONLINE = OFF ) 

GO 

--然后创建非聚集索引

USE [test] 

GO 

CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[test] 

( 
[companyid] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

GO 

-- 测试脚本，使用 nvarchar(100) 测试 nvarchar(100) 与非聚集索引结合使用时行为没有变化

declare @k nvarchar(1000) --–-or max 

set @k = '' 

SELECT @k = @k + REPLACE(REPLACE(TagName,'<',''),'>','') + ',' FROM test WITH (NOLOCK) 
WHERE CompanyID = 1 ORDER BY fieldnumber 


select @k as test_result_with_nonClustered_index_and_varchar1000_combine 

go 

Answer 1

此查询从未保证有效。创建聚集索引后的查询计划如下所示。

使用 FOR XML(解决方法和正确方法)可以保证订单，因为它始终在查询正确完成后进行处理。但是，通过 @var concat 查询，SQL Server 可以根据需要自由优化查询。

在这里您可以看到 SELECT 语句的串联是在排序之前完成的，因此可以按任何顺序串联行，返回的不一定是最长的(最终的)。

我更喜欢显示表达式的文本计划，所以这里是:

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------
  |--Sort(ORDER BY:([test].[dbo].[test].[fieldnumber] ASC))
       |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(nvarchar(1000),([@k]+[Expr1006])+N',',0)))
            |--Compute Scalar(DEFINE:([Expr1006]=replace(replace([test].[dbo].[test].[Tagname],N'<',N''),N'>',N'')))
                 |--Clustered Index Seek(OBJECT:([test].[dbo].[test].[ix-test]), SEEK:([test].[dbo].[test].[companyid]=(1)) ORDERED FORWARD)

REPLACE x2 和 CONCAT 操作被放在一起，表面上是为了性能。
但是，如果您将查询更改为

declare @k nvarchar(1000) --–-or max 
set @k = '' 
SELECT @k = @k + REPLACE(REPLACE(TagName,'<',''),'>','') + ','
FROM (
    select TOP(100) TagName, fieldnumber
    from test WITH (NOLOCK)
    WHERE CompanyID = 1
    order by fieldnumber
    ) X
order by fieldnumber
select @k as test_result_with_clustered_index_varchar1000_combine 

您可以看到，在执行 CONCAT 之前，SQL Server 被迫在子查询中进行排序。 注意: TOP 100 PERCENT 不起作用，因为它已经被优化掉了，但是 TOP(N) 可以起作用，其中 N 大于表中的记录数。然而，正确的解决方案是使用 FOR XML。

StmtText
---------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(nvarchar(1000),([@k]+[Expr1006])+N',',0)))
       |--Sort(TOP 100, ORDER BY:([test].[dbo].[test].[fieldnumber] ASC))
            |--Compute Scalar(DEFINE:([Expr1006]=replace(replace([test].[dbo].[test].[Tagname],N'<',N''),N'>',N'')))
                 |--Clustered Index Seek(OBJECT:([test].[dbo].[test].[ix-test]), SEEK:([test].[dbo].[test].[companyid]=(1)) ORDERED FORWARD)