September 16, 2009 at 8:14 am
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
September 16, 2009 at 8:19 am
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.
September 16, 2009 at 8:30 am
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
September 16, 2009 at 8:43 am
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