Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by mverma4you » SELECT FUN
Add to briefcase
35 posts, Page 1 of 4
1234»»»

SELECT FUN Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
mverma4you
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: Friday, November 09, 2012 8:46 AM
Points: 459, Visits: 180
Comments posted to this topic are about the item SELECT FUN
Post #702024
Carlo Romagnano
Posted Wednesday, April 22, 2009 12:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:04 AM
Points: 1,968, Visits: 1,819
this question is the same of "predict output".
Also the second answer is correct, cause of lack of ORDER BY.
Post #702074
Hugo Kornelis
Posted Wednesday, April 22, 2009 1:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 5,230, Visits: 7,020
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
Rachel Byford
Posted Wednesday, April 22, 2009 1:49 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 1,538, Visits: 799
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
Hugo Kornelis
Posted Wednesday, April 22, 2009 2:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:01 AM
Points: 5,230, Visits: 7,020
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
Rachel Byford
Posted Wednesday, April 22, 2009 2:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 1,538, Visits: 799
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
EdVassie
Posted Wednesday, April 22, 2009 3:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:57 AM
Points: 2,619, Visits: 2,749
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.

Author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2012, 2008 R2, 2008 and 2005. 25 March 2013: now over 23,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
Atif-ullah Sheikh
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: Thursday, March 14, 2013 4:15 AM
Points: 3,240, Visits: 4,960
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
dave.farmer
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, May 03, 2013 3:39 AM
Points: 857, Visits: 584
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
mike-658424
Posted Wednesday, April 22, 2009 5:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 7:21 AM
Points: 99, Visits: 201
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
35 posts, Page 1 of 4
1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.