CODEWITHSUNDEEP
sqlsql-servertemp-tables
Rexilion
Rexilion

Reputation: 13

Adding index to temporary table results in single row result set

Inside an sp_executesql a select statement is used to concatenate a variable by itself and the value of a column from an temporary table.

This gives weird results when I apply an index to this temporary table and use ordering in the aforementioned select statement.

This only happens when the amount of rows of the temporary table is bigger than 50 rows.

I don't like pasting big code examples, but I was not able to reduce it any further.

If @maxjob >= 8, then the resulting @htmllog contains 67 characters. This is an unexpected result.

If @maxjob < 8, then the resulting @htmllog always contain more than 67 characters. This is the expected result.

Furthermore,

When I remove the index idx_key from the #acl_log table, then the issue disappears when @maxjob >= 8. or When I remove the 'order by [key] asc' from @executesql_sql then the issue disappears as well.

Why?

declare @logtable as varchar(max)
set @logtable = '#acl_log'

if (OBJECT_ID('[tempdb]..#acl_log')) is not null
    drop table #acl_log

create table #acl_log(
    [key]       int             identity,
    [message]   nvarchar(max)   not null,
    index idx_key ([key])
)

declare @job as int
declare @maxjob as int

set @job = 0
set @maxjob = 8

while (@job < @maxjob + 1)
begin
    insert into #acl_log([message])
    values
        ('Internet Explorer is currently running without add-ons')
        ,('All Internet Explorer add-ons, such as ActiveX controls or toolbars, are turned off. Some webpages might not display correctly.')
        ,('To continue to your home page, click the Home button.')
        ,('To browse using add-ons, close Internet Explorer and then start it again.')
        ,('Forward Arrow  Check for the latest Windows updates. ')
        ,('Question Icon How do browser add-ons affect my browsing experience? ')

    set @job = @job + 1
end

declare @executesql_sql as nvarchar(max)
declare @executesql_param as nvarchar(max)

declare @htmllog as varchar(max)
set @executesql_sql = '
    set @htmllog = ''''
    select @htmllog = @htmllog + [message]
    from ' + @logtable + '
    order by [key] asc'
set @executesql_param = '@htmllog varchar(max) output'

exec master..sp_executesql @executesql_sql, @executesql_param, @htmllog = @htmllog output

select len(@htmllog), @htmllog

Upvotes: 0

Views: 231

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46213

The behavior of aggregate string concatenation is undefined because it is plan-dependent according the comment by Microsoft in this connect item:

Even without ORDER BY, we do not guarantee that @var = @var + will produce the concatenated value for any statement that affects multiple rows. The right-hand side of the expression can be evaluated either once or multiple times during query execution and the behavior as I said is plan dependent.

One workaround is the FOR XML clause:

DECLARE @logtable AS varchar(MAX);
SET @logtable = '#acl_log';

IF (OBJECT_ID('[tempdb]..#acl_log')) IS NOT NULL
    DROP TABLE #acl_log;

CREATE TABLE #acl_log(
    [key]       int             IDENTITY,
    [message]   nvarchar(max)   not null,
    INDEX idx_key ([key])
);

DECLARE @job as int;
DECLARE @maxjob as int;

SET @job = 0;
SET @maxjob = 8;

WHILE (@job < @maxjob + 1)
BEGIN
    INSERT INTO #acl_log([message])
    VALUES
        ('Internet Explorer is currently running without add-ons')
        ,('All Internet Explorer add-ons, such as ActiveX controls or toolbars, are turned off. Some webpages might not display correctly.')
        ,('To continue to your home page, click the Home button.')
        ,('To browse using add-ons, close Internet Explorer and then start it again.')
        ,('Forward Arrow  Check for the latest Windows updates. ')
        ,('Question Icon How do browser add-ons affect my browsing experience? ');

    SET @job = @job + 1;
END

DECLARE @executesql_sql AS nvarchar(MAX);
DECLARE @executesql_param AS nvarchar(MAX);

DECLARE @htmllog AS varchar(max);
SET @executesql_sql = '
    SET @htmllog = (SELECT [message]
    FROM ' + @logtable + '
    ORDER BY [key]
    FOR XML PATH(''''), TYPE).value(''.'', ''varchar(MAX)'');';

set @executesql_param = '@htmllog varchar(max) OUTPUT';

EXEC master..sp_executesql @executesql_sql, @executesql_param, @htmllog = @htmllog output;

SELECT LEN(@htmllog), @htmllog;
GO

Upvotes: 3

Related Questions