2

I was looking at this post of trim all columns of a table. In the answer query script the column names gets concatenated without any looping operation. I simplified it to check its work flow as explained below.

  • Create a new database

  • Create a new table with three columns as in below script.(insert values not required)

    Create table table1(id int, name varchar(20), city varchar(20))
    

    Execute below T-SQL Script, which results xxx [id] [name] [city] in single row.

    Declare @a as varchar(4000)
    SET @a = 'xxx '
    SELECT @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 
    SELECT @a
    

Where as below script results in 3 rows

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT 'b' = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 

To understand more better I executed the below query using Top 1. This results xxx [id]

Declare @a as varchar(4000)
SET @a = 'xxx '
SELECT Top 1 @a = @a + '['+COLUMN_NAME+'] ' FROM INFORMATION_SCHEMA.COLUMNS 
SELECT @a

I got puzzled With Top 2 query. This resulted xxx [id] [name].

Where as I was expecting xxx [id] xxx [id] [name] i.e. First value + second value. Where am I going wrong.

I am curious to know how the column names getting concatenated. Sorry I could not frame better title for this puzzle.

3
  • 2
    First of all, please stop using single quotes around column aliases (as in 'b' = ...) – that is just bad. Secondly, what does the 'b' = ... example have to do with your question anyway? Or are you also confusing cases like SELECT columnalias = ... and SELECT @variablename = ...? You should probably ask about those separately.
    – Andriy M
    Mar 6, 2015 at 14:22
  • What is the expected results for TOP(3)? Mar 6, 2015 at 14:33
  • @AndriyM - Aaron Bertrand 's article was informative. My clear question is without looping how values are getting concatenated. You can ignore 'b'=... query part. I just put that code for comparison or some reference.
    – Shiva
    Mar 6, 2015 at 15:09

2 Answers 2

7

I am curious to know how the column names getting concatenated.

Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.

The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.

From SET @local_variable (Transact-SQL)

Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row.

Instead you can use a cursor or the for xml trick to do the same.

declare @a as varchar(4000);
set @a = 'xxx ';

set @a = @a + (
              select quotename(COLUMN_NAME) as '*'
              from INFORMATION_SCHEMA.COLUMNS
              for xml path(''), type
              ).value('text()[1]', 'varchar(4000)');

Result: xxx [city][id][name]

4
  • Though, if we follow to the KB artible, it states You may encounter unexpected results when you apply any operators or expressions to the ORDER BY clause of aggregate concatenation queries, which is not the case. This is just a simple concatenation which works as expected Mar 6, 2015 at 14:41
  • @user16484 I can think of at least three other "expected" results with a syntax like that and the text in the documentation does not say that it works nor how it works when you omit the order by clause. Mar 6, 2015 at 15:03
  • You're right as to the order of the results as we are not forcing them;) still, in a concatenation without any further expressions/operations, I can't see how the result could be different in the sense of 'xxx ' appearing more than once; they do state that the unexpected results is an issue when you apply any operators or expressions to the ORDER BY clause Mar 6, 2015 at 15:10
  • 1
    @user16484 the KB article only mentions order by but the responses on the connect site indicate this is not supported or guaranteed ever. Some example quotes here stackoverflow.com/a/15163136/73226 especially 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. Mar 6, 2015 at 23:59
0

After several experiments I am here answering my same question. This works as our traditional a = a + 1 method.

Run queries one by one as below to understand the flow of task.

Example1:

Query 1: Below query returns 1@[id]. Meaning @a = 1@[id]

Declare @a as varchar(4000)
SET @a = 1
SELECT Top 1 @a = @a + '@' + '['+COLUMN_NAME+']'  FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

Query 2: Below query returns 1@[id]@[name]. Meaning @a = 1@[id] + @ + [name]

Declare @a as varchar(4000)
SET @a = 1
SELECT Top 2 @a = @a + '@' + '['+COLUMN_NAME+']' FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

Query 3: Below query returns 1@[id]@[name]@[city]. Meaning @a = 1@[id]@[name] + @ + [City]

Declare @a as varchar(4000)
SET @a = 1
SELECT Top 3 @a = @a + '@' + '['+COLUMN_NAME+']'  FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

Example 2:

You can run top 1, top 2, top 3 queries for the below and know the flow. This is going to return '# ' 8 times. This query illustration is without column names.

Declare @a as varchar(4000)
SET @a = '# '
SELECT Top 3 @a = @a + @a  FROM INFORMATION_SCHEMA.COLUMNS
SELECT @a

OUTPUT : # # # # # # # #

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.