Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

SELECT FUN Expand / Collapse
Author
Message
Posted Tuesday, April 21, 2009 9:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, October 10, 2013 10:15 AM
Points: 459, Visits: 182
Comments posted to this topic are about the item SELECT FUN
Post #702024
Posted Wednesday, April 22, 2009 12:52 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 6:53 AM
Points: 2,393, Visits: 2,286
this question is the same of "predict output".
Also the second answer is correct, cause of lack of ORDER BY.
Post #702074
Posted Wednesday, April 22, 2009 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 5,795, Visits: 8,011
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: 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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #702091
Posted Wednesday, April 22, 2009 1:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:25 AM
Points: 1,614, Visits: 888
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.
Post #702104
Posted Wednesday, April 22, 2009 2:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 5,795, Visits: 8,011
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!



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #702117
Posted Wednesday, April 22, 2009 2:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, March 10, 2014 2:25 AM
Points: 1,614, Visits: 888
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.
Post #702120
Posted Wednesday, April 22, 2009 3:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:21 AM
Points: 2,797, Visits: 3,079
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: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2014, 2012, 2008 R2, 2008 and 2005. 25 March 2014: now over 28,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #702146
Posted Wednesday, April 22, 2009 3:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 AM
Points: 3,241, Visits: 4,987
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...



----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.

You can check my BLOG here

Post #702152
Posted Wednesday, April 22, 2009 4:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, March 28, 2014 3:57 AM
Points: 867, Visits: 641
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?
Post #702200
Posted Wednesday, April 22, 2009 5:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, January 18, 2014 3:41 AM
Points: 107, Visits: 203
Hey! Not fair. I lost a point because of a missing ORDER BY ? Like a previous poster mentioned, both 1 and 2 are correct.

Can I get my point back please?


Post #702229
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse