Get a couple fruits

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719096

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

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71756

    Nice one, thanks Steve

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

  • Shayn Thomas


    Points: 5602

    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

  • gvoshol 73146

    Hall of Fame

    Points: 3175

    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  🙂


  • rustman

    SSCarpal Tunnel

    Points: 4130

    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.

  • mvarey


    Points: 21

    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.

  • Fred_unique

    SSC Enthusiast

    Points: 151

    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.

  • ScottPletcher

    SSC Guru

    Points: 98427

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719096

    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.


  • Data_God


    Points: 2771

    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.
  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719096

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

  • David Conn


    Points: 5751


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

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