Can Clustered index change dynamic SQL statement behavior and return different results?

Question

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

recently, I found create clustered index in one table and this clustered index changed the results of a dynamic SQL statement. When the table have clustered index, the statement only returned the last filed result. drop the clustered index from the table or delete the 'ORDER BY fieldnumber', will return the full result (15 fields). The change in behavior was caused by the clustered index in conjunction with the replace calls, as well as the order by, and the declaration of k being 1000 vs max in the sample statement.

Can the Clustered index change dynamic SQL statement behavior and return different results or something else I don't know? Any comments are welcome!

--Create the test table

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 test data

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 

-- test script

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 one clustered index to test the above script

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 

-- test script which with nvarchar(1000)to test the behavior change with clustered index

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 

-- test script which with nvarchar(max)to test the behavior no change under nvarchar (max) combine with clustered index

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 

--drop the clustered index

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 

-- then create nonclustered index

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 

-- test script which with nvarchar(100)to test the behavior no change under nvarchar (100) combine with NONclustered index

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

This query has never been guaranteed to work. The following shows the query plan after creating the clustered index.

The order is guaranteed using FOR XML (the workaround and correct approach) because that is always processed AFTER the query-proper completes. However, with the @var concat query, SQL Server is free to optimize the query as it sees fit.

Here you can see that the concatenations for the SELECT statement are done prior to the sorting, so the row can be concatenated in any order, with the one returned not necessarily the longest (final).

I prefer text plans, which shows the Expressions, so here it is:

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)

The REPLACE x2 and CONCAT operations are being put together, ostensibly for performance.
However, if you change the query to

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 

You can see that SQL Server is forced to SORT in the subquery before performing the CONCAT. Note: TOP 100 PERCENT doesn't work as it gets optimized away, but TOP(N) where N is greater than the number of records in the table will work. The correct solution however, is to use 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)