April 21, 2009 at 9:24 pm
Comments posted to this topic are about the item SELECT FUN
April 22, 2009 at 12:52 am
this question is the same of "predict output".
Also the second answer is correct, cause of lack of ORDER BY.
April 22, 2009 at 1:23 am
Carlo Romagnano (4/22/2009)
Also the second answer is correct, cause of lack of ORDER BY.
Agreed. Since there is no ORDER BY, both answer options #1 and #2 are correct. I chose the first, at random, and got my point. I then ran the code to see that, on my computer, my version of SQL Server, the current service pack and hotfix level, and the current workload on the machine, this was indeed the result - but there is no guarantee at all that the same ordering of rows willl be reproduced the next time I run this code.
Not a bad question for understanding that UNION removes duplicates, and that two NULLs, even though they don't compare as equal, are considered duplicate - but a very bad question for including two versions of the same answer, in a different order, and only showing one of tem as correct.
@steve-2: Probabaly the best way to fix this is to change the question to read: "what can be the output of this code (check all that apply)", and mark both answers #1 and #2 as correct.
April 22, 2009 at 1:49 am
My belief was that as a consequence of the union removing duplicates, the output always came out ordered. Possibly not if you end up with several worker processes doing the work, but in this case that won't happen.
April 22, 2009 at 2:13 am
Rachel Byford (4/22/2009)
My belief was that as a consequence of the union removing duplicates, the output always came out ordered. Possibly not if you end up with several worker processes doing the work, but in this case that won't happen.
Hi Rachel,
Several workers processing the output is extremely unlikely, given the small amount of data. But the method of removing duplicates can affect the results. SQL Server has different strategies for removing duplicates. The one you are thinking of uses a Sort step to bring duplicates together, followed by a Stream Aggregate step to remove them. This one is not used by this query (at least not on my machine; as I already indicated, this depends on a lot of factors and results may vary based on different versions of SQL Server, different hardware, and different workload).
The technique ussed on my computer (I checked by running the query with the "include actual execution plan" option activated) is to use a merge join between three one-row "tables". Due to how merge works, this will also cause the result set to be sorted.
A third technique for removing duplicates is a hash union. You can force SQL Server to use this technique by adding "OPTION (HASH UNION)" at the end of the query. On my machine, this changed the order of the output rows (though it might not have this effect on all machines, as the order now depends on the actual hashing function used, which is as far as I know not documented and might change between versions or even between service packs).
These are the three techniques for union that I am aware of. There may be more. And if there are no more now, they might be introduced at a later time, if someone in the SQL Server optimizer team comes up with a bright idea.
I can only repeat what I already said a million times: order of rows is never guaranteed, unless an explicit ORDER BY is present on the outermost query!
April 22, 2009 at 2:19 am
Very interesting, thank you.
I agree absolutely with your statement that if you want something order it, then you should always specify an order by clause.
April 22, 2009 at 3:04 am
I think previous posters have said all that is needed. Either option 1 or option 2 are correct as there is no ORDER BY clause. It would be interesting to run this query a few thousand times on a very busy server with at least 4 cores, to see if thre is a 50-50 split between the two possible results.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 22, 2009 at 3:21 am
On seeing the question, i thought there might be a Default Order of the result set returned by the query without the order by clause. But after a research, I came to know (and I was sure about the results) that there is no guarantee of ordering of data returned by the query without the Order by clause along with the fact that the queried table DO NOT have any Index on it.
Half heartedly selected the First option and got the point...:-D
April 22, 2009 at 4:54 am
I feel cheated - I chose the second so got it wrong, but that seems harsh to me as the data is unordered, isn't it?
April 22, 2009 at 5:43 am
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:
Can I get my point back please?
April 22, 2009 at 6:26 am
mike (4/22/2009)
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:Can I get my point back please?
Yeah, me too!
April 22, 2009 at 6:26 am
From Microsoft: "The exact results of a UNION operation depend on the collation chosen during installation and the ORDER BY clause."
http://msdn.microsoft.com/en-us/library/ms191141(SQL.90).aspx
This is for SQL Server 2005. I'm assuming it applies to other versions as well. It appears that behind the scenes SQL Server does sort it as part of the duplicate elimination process. This will not be done with UNION ALL though.
For the record: An ORDER BY should be used as part of best practices.
Cheers,
Brian
April 22, 2009 at 7:49 am
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 2
or
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 1
gives you same result.
NULL1
NULLmanoj
I tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.
SQL DBA.
April 22, 2009 at 9:52 am
Jayeff (4/22/2009)
mike (4/22/2009)
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct. :crying:Can I get my point back please?
Yeah, me too!
And see, you both got you point just by posting that you want your point back. 😉
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
April 22, 2009 at 10:43 am
SanjayAttray (4/22/2009)
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'order by 2
or
select NULL,'manoj'union select Null,'1'union select NULL,'manoj'
order by 1
gives you same result.
NULL1
NULLmanoj
I tried on all sql server versions and result is same. May be I am missing something here when some people are telling both 1 & 2 answers are correct.
The point is that, as documented, the order of results from a union without an explicit ORDER BY cannot be guaranteed. As Hugo pointed out, it is possible under some circumstances to get a different order, hence, in theory, both answers are correct.
Of course, adding 'ORDER BY 2 DESC' forces answer 2. 🙂
Derek
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply