Get a couple fruits

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

  • Nice one, thanks Steve

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

  • 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

  • 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  🙂


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

  • 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.

  • 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.

  • 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • 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.


  • 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?

    You must be logged in to view attached files.
  • There is no guarantee of ordering when selecting from a table without an ORDER BY

  • Caching

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

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