why is query sorted.  
Author Message
KimDFlorida





PostPosted: Wed Jan 05 15:02:07 CST 2005 Top

SQL Server Developer >> why is query sorted.

If I run the following query in QA, the result is sorted alphabetically. I
do not want it sorted. I would have thought that a union would just pile
one on top of another without reorganizing them. Why is this being sorted?

select 'add'
union
select 'removed'
union
select 'changed'

SQL Server98  
 
 
Adam





PostPosted: Wed Jan 05 15:02:07 CST 2005 Top

SQL Server Developer >> why is query sorted. Try UNION ALL instead. UNION ensure distinctness. SQL Server is sorting
the rows in order to figure out if there are any duplicates.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--




> If I run the following query in QA, the result is sorted alphabetically.
I
> do not want it sorted. I would have thought that a union would just pile
> one on top of another without reorganizing them. Why is this being
sorted?
>
> select 'add'
> union
> select 'removed'
> union
> select 'changed'
>
>
>


 
 
Armando





PostPosted: Wed Jan 05 15:05:07 CST 2005 Top

SQL Server Developer >> why is query sorted. From BOL:

"The exact results of a UNION operation depend on the collation"



> If I run the following query in QA, the result is sorted alphabetically.
I
> do not want it sorted. I would have thought that a union would just pile
> one on top of another without reorganizing them. Why is this being
sorted?
>
> select 'add'
> union
> select 'removed'
> union
> select 'changed'
>
>
>


 
 
Aaron





PostPosted: Wed Jan 05 15:09:57 CST 2005 Top

SQL Server Developer >> why is query sorted. You didn't include an ORDER BY clause. So you shouldn't expect any specific
order, by definition. Without an ORDER BY clause, you are telling the
engine that you will accept the results in any order it chooses.

If you want to guarantee a specific order, here are several options:

-- order alphabetically
select y = 'add'
union
select 'removed'
union
select 'changed'
order by y

-- order in the order listed, two different versions
select y = 'add'
union all
select 'removed'
union all
select 'changed'

select y from
(
select x=1, y = 'add'
union all
select 2, 'removed'
union all
select 3, 'changed'
) t
order by x

-- random order
select y from
(select y = 'add'
union all
select 'removed'
union all
select 'changed'
) t
order by newid()

--
http://www.aspfaq.com/
(Reverse address to reply.)






> If I run the following query in QA, the result is sorted alphabetically.
I
> do not want it sorted. I would have thought that a union would just pile
> one on top of another without reorganizing them. Why is this being
sorted?
>
> select 'add'
> union
> select 'removed'
> union
> select 'changed'
>
>
>


 
 
David





PostPosted: Wed Jan 05 15:45:53 CST 2005 Top

SQL Server Developer >> why is query sorted. The query is unsorted (in other words the order is undefined) unless you
specify ORDER BY. If you want some other order then you need to add ORDER
BY.

--
David Portas
SQL Server MVP
--


 
 
Kalen





PostPosted: Wed Jan 05 16:00:49 CST 2005 Top

SQL Server Developer >> why is query sorted. There are actually several algorithms that SQL Server can use to remove
duplicates, and sorting after combining the rows is one of those algorithms,
and it will produce sorted results. Unless you use a hint, you usually can't
predict what union algorithm will be used. SHOWPLAN will show you the
algorithm used.

With a hint, you can results in a different order:

select 'add'
union
select 'removed'
union
select 'changed'
option (hash union)

Or, as Adam suggested, if you use UNION ALL, you are telling SQL Server NOT
to remove duplicates, so no sorting will need to be done, unless you
specifically ask for sorted results with an ORDER BY.
--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com




> Try UNION ALL instead. UNION ensure distinctness. SQL Server is sorting
> the rows in order to figure out if there are any duplicates.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>


>> If I run the following query in QA, the result is sorted alphabetically.
> I
>> do not want it sorted. I would have thought that a union would just pile
>> one on top of another without reorganizing them. Why is this being
> sorted?
>>
>> select 'add'
>> union
>> select 'removed'
>> union
>> select 'changed'
>>
>>
>>
>
>


 
 
Peter





PostPosted: Wed Jan 05 16:36:17 CST 2005 Top

SQL Server Developer >> why is query sorted. The problem is that I do not want is sorted alphabetically but rather in the
order that I specify then. What should be returned is a one-column table so
I cannot have an extra column where I hardcode some numeric value to force
the sort.

SlqServer is already sorting them and I do not want it to.




> You didn't include an ORDER BY clause. So you shouldn't expect any
specific
> order, by definition. Without an ORDER BY clause, you are telling the
> engine that you will accept the results in any order it chooses.
>
> If you want to guarantee a specific order, here are several options:
>
> -- order alphabetically
> select y = 'add'
> union
> select 'removed'
> union
> select 'changed'
> order by y
>
> -- order in the order listed, two different versions
> select y = 'add'
> union all
> select 'removed'
> union all
> select 'changed'
>
> select y from
> (
> select x=1, y = 'add'
> union all
> select 2, 'removed'
> union all
> select 3, 'changed'
> ) t
> order by x
>
> -- random order
> select y from
> (select y = 'add'
> union all
> select 'removed'
> union all
> select 'changed'
> ) t
> order by newid()
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>


> > If I run the following query in QA, the result is sorted alphabetically.
> I
> > do not want it sorted. I would have thought that a union would just
pile
> > one on top of another without reorganizing them. Why is this being
> sorted?
> >
> > select 'add'
> > union
> > select 'removed'
> > union
> > select 'changed'
> >
> >
> >
>
>


 
 
Aaron





PostPosted: Wed Jan 05 16:47:22 CST 2005 Top

SQL Server Developer >> why is query sorted. > The problem is that I do not want is sorted alphabetically but rather in
the
> order that I specify then. What should be returned is a one-column table
so
> I cannot have an extra column where I hardcode some numeric value to force
> the sort.

Did you actually TRY my two middle suggestions? Neither returns any extra
columns.

> SlqServer is already sorting them and I do not want it to.

There have been several potential solutions suggested here, have you tried
any of them, or just glanced them over?

--
http://www.aspfaq.com/
(Reverse address to reply.)


 
 
Adam





PostPosted: Wed Jan 05 21:19:42 CST 2005 Top

SQL Server Developer >> why is query sorted.

> There are actually several algorithms that SQL Server can use to remove
> duplicates, and sorting after combining the rows is one of those
algorithms,
> and it will produce sorted results. Unless you use a hint, you usually
can't
> predict what union algorithm will be used. SHOWPLAN will show you the
> algorithm used.


Thanks, Kalen. Great info.

--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--


 
 
David





PostPosted: Thu Jan 06 00:19:51 CST 2005 Top

SQL Server Developer >> why is query sorted.
> If I run the following query in QA, the result is sorted
> alphabetically. I do not want it sorted. I would have thought that
> a union would just pile one on top of another without reorganizing
> them. Why is this being sorted?
>
> select 'add'
> union
> select 'removed'
> union
> select 'changed'

Use UNION ALL, not UNION. UNION requires removing matching rows and in
order to fo that SQL Server must sort the data first, which adds
unnecessary overhead.



--
David Gugick
Imceda Software
www.imceda.com