Post reply

Get a couple fruits

  • March 17, 2020 at 12:00 am

    #3731588

    Comments posted to this topic are about the item Get a couple fruits

  • March 17, 2020 at 4:08 am

    #3734896

    Nice one, thanks Steve

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    “libera tute vulgaris ex”

  • March 17, 2020 at 8:12 am

    #3734914

    got this one wrong, should have known really, but still all my tests returned apple and pear

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • March 17, 2020 at 11:13 am

    #3734937

    Often queries will return the "first" x items.  But just as a query without an ORDER BY doesn't necessarily return the "first" items, neither does STRING_SPLIT always return items in order.

    I'll bet Jeff Moden got this one right  🙂

     

  • March 17, 2020 at 12:39 pm

    #3734976

    Good lesson on general query results...so often returned in what we think they should be but in reality no guarantee of that without the order by clause.

  • March 17, 2020 at 1:10 pm

    #3734986

    I don't think it will ever return anything but apple and pear since it is not a table, but a string list.  If it were a table, then the answer would be correct, but since it is NOT a table, it will always return apple and pear.

  • March 17, 2020 at 1:49 pm

    #3735012

    But string_split returns a table, to which the select is applied.  So order is not guaranteed.  The documentation emphasizes that.  I know, every test returns the expected order.  It would be when I depend on it in production that something would go wrong.

  • March 17, 2020 at 2:42 pm

    #3735037

    You really should start using the new syntax, with parentheses around the row count:

    SELECT TOP (2) *

    FROM STRING_SPLIT(@s, ',') AS ss

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • March 17, 2020 at 2:54 pm

    #3735041

    Good catch, Scott. Changing that.

    String_split returns a table. The list is converted into a table, so normal query rules for a table apply. In this case, there is no PK, and no ordering guaranteed. In this small sample, it typically is the same, but it could be different, depending on how this "table" gets held in memory or spilled to disk. No one should expect ordering without an ORDER BY in a query against a table.

     

  • March 23, 2020 at 3:42 pm

    #3736296

    Maybe I'm just being dense here but I got the question wrong and then started reading through the comments. I did some testing where I issued the query, issued the query with a GO 10 (Screenshot included) a GO 100 (No screenshot too long to show all results) and a GO 1000 (Again no screenshot, same reason).

     

    In  every case I got back Apple as line 1 and pear as line 2.

    What am I missing?

    Attachments:
    You must be logged in to view attached files.

  • March 23, 2020 at 3:59 pm

    #3736307

    There is no guarantee of ordering when selecting from a table without an ORDER BY

  • April 1, 2020 at 6:40 am

    #3739520

    Caching

Viewing 12 posts - 1 through 11 (of 11 total)

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