SELECT FUN

  • mverma4you

    Ten Centuries

    Points: 1013

    Comments posted to this topic are about the item SELECT FUN

  • Carlo Romagnano

    SSC-Insane

    Points: 21743

    this question is the same of "predict output".

    Also the second answer is correct, cause of lack of ORDER BY.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Rachel Byford

    Hall of Fame

    Points: 3437

    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.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Rachel Byford

    Hall of Fame

    Points: 3437

    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.

  • EdVassie

    SSC Guru

    Points: 60266

    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

  • Atif-ullah Sheikh

    SSChampion

    Points: 12495

    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

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

  • dave.farmer

    SSCrazy

    Points: 2371

    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?

  • mike-658424

    SSC Enthusiast

    Points: 187

    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?

  • Jayeff

    SSC Veteran

    Points: 200

    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!

  • beezell

    Hall of Fame

    Points: 3112

    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

  • SanjayAttray

    SSChampion

    Points: 13157

    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.

    NULL 1

    NULL manoj

    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.

  • Ronald H

    SSCarpal Tunnel

    Points: 4022

    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

  • StarNamer

    SSCrazy Eights

    Points: 8633

    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.

    NULL 1

    NULL manoj

    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 35 total)

You must be logged in to reply to this topic. Login to reply