Unexpected string concatenation result

Question

Imagine the following schema:

create table tempdb..t1 (id int, name sysname); 
create table tempdb..t2 (id int, name sysname); 
create index IX1 on tempdb..t1 (id); 
create index IX2 on tempdb..t2 (id);

Now I'm trying to script index definition:

declare @stmt nvarchar(max) = '';
select @stmt += 'create index ' + ix.name + ' on ' + t.name 
    + isnull(' where ' + ix.filter_definition, '') + char(13)
    from tempdb.sys.tables t
    join tempdb.sys.indexes ix on t.object_id = ix.object_id
    where ix.type > 0 and t.name in ('t1','t2')
    order by ix.name;
print @stmt;

I'm expecting to get two index definitions:

create index IX1 on t1
create index IX2 on t2

but get only second. If I remove order by or isnull part, or add top statement, I get both definitions.

Am I missing something obvious?

This approach to concatenation is not guaranteed to work. See possible duplicate of [nvarchar concatenation / index / nvarchar(max) inexplicable behavior](http://stackoverflow.com/questions/15138593/nvarchar-concatenation-index-nvarcharmax-inexplicable-behavior) — Martin Smith, Apr 04 '13 at 12:29

score 0 · Answer 1 · edited May 23 '17 at 11:49

0

When you use select to put a value into a scalar variable only one row will be put in the variable. It won't aggregate your two rows (or however large your result set is) into one row.

Imagine you have a variable which is an integer and you try to assign 700 different values to it, that wouldn't work. Same thing with trying to assign two strings to the same variable. Instead only one of value would be used.

declare @i int;

SELECT @i = t.value
FROM myTable AS t
WHERE t.value between 1 and 700

SELECT @i

A good way to create a pseudo string concatenation aggregate function is to use the XML functionality with FOR XML PATH. Check out this question: How to concatenate results in T-SQL into column?

edited May 23 '17 at 11:49

Community

1
1

answered Apr 04 '13 at 13:43

David Söderlund

978
8
14

Aggregation works if I remove `order by` clause. But correct result is not guaranteed. Check out Martin's link. – Alexey A. Apr 04 '13 at 14:10
1

I think I need to read up on concatenation. I thought it simply didn't work on 2008 R2. – David Söderlund Apr 04 '13 at 18:57

score 0 · Answer 2 · answered Apr 05 '13 at 10:49

I am flabbergasted this behavior is by design.

To summarize information from Martin’s link: don’t do concatenation like this. It is not guaranteed to work even without order by. To get result that is guaranteed to work, use for xml.

declare @stmt nvarchar(max) = '';
select @stmt = (select  
    'create index ' + ix.name + ' on ' + t.name 
    + isnull(' where ' + ix.filter_definition, '') + char(10)
    from tempdb.sys.tables t
    join tempdb.sys.indexes ix on t.object_id = ix.object_id
    where ix.type > 0 and t.name in ('t1','t2')
    order by ix.name
    for xml path(''));
print @stmt;

Other options are using cursors or SQLCLR.

Unexpected string concatenation result

2 Answers2