Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2012-03-05 : 10:43:57
|
| Hey, nothing major here but I just couldn't get my head straight on this one.I am looking for some explanations.I have this working scriptSelect 'John' as username, col1, clo2...where SDate between '2012-02-25' and '2012-03-05'UnionSelect 'Jane' as username, col1, clo2...where SDate between '2012-02-25' and '2012-03-05'Union....It came back with all John's rows then Jane's rows.Then I replaced with thisDelcare @StartDate as char(10);Delcare @EndDate as char(10);Set @StartDate = '2012-02-25' Set @EndDate = '2012-03-05'Select 'John' as username, col1, clo2...where SDate between @StartDate and @EndDate UnionSelect 'Jane' as username, col1, clo2...where SDate between @StartDate and @EndDate Union....It gave me the same rows, but the sorting is off. I could add sort by username at the end to make it generate the same result. But why? I would love to hear a good explanation. Thanks! |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2012-03-05 : 10:48:04
|
| The sort order isn't guaranteed (for any query) unless you give an ORDER BY clause.the reason you'll get different orders is *probably* down to parallelism in the different query plans. Especially with UNION queries you get partitioned streams delivering data and then collated. without an order by the order is just as data streams in it is output.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-03-05 : 10:51:17
|
apart from the fact that there is no reliable order without using ORDER BY...The first example has fixed valuesThe second example has variable valuesThat can lead to different execution plans on using indexes or not and so on - hence there can be a difference in the order...
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
|
|
|
|
help with union did not give the right sort order