Concatenating a column to a variable

  • Does anybody know why I'm getting different results from (apparently) the same queries?

    When I order by some specific column I get a concatenated string, when I try referring the column by ordinal position I get one single value in the variable.

    What does ORDER BY 1 mean in this context? Is the variable somehow involved?

    declare @id_string varchar(5000)

    set @id_string = ''

    --ORDER BY SPECIFIC COLUMN: CONCATENATION HAPPENS

    select top 100 @id_string = @id_string + cast(id as varchar(50)) + ' ,'

    from sysobjects

    order by id

    print @id_string

    set @id_string = ''

    --ORDER BY FIRST COLUMN: NO CONCATENATION

    select top 100 @id_string = @id_string + cast(id as varchar(50)) + ' ,'

    from sysobjects

    order by 1

    print @id_string

    -- Gianluca Sartori

  • The execution plans are quite different but I am not an expert at reading these so I will leave that for someone else.

    1 does a merge join (concatenation) and the other just does a concetenation.

  • The plans are different because sysobjects is already ordered by id (it's the clustered index for the table).

    In the second query the expression in the order by is the whole id + ' ,' thing, so there's an additional order by, but this doesn't justify the missing concatenation.

    Why is this happening?

    -- Gianluca Sartori

  • Interesting.... not sure as it seems like it should perform the same action. Per books online I saw this...

    A sort column can be specified as a name or column alias, or a nonnegative integer representing the position of the name or alias in the select list. An integer cannot be specified when the order_by_expression appears in a ranking function

    Is the TOP considered (or performing behind the scenes) a ranking function? Just a thought...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply