locked
SELECT and order of data returned...... RRS feed

  • Question

  • I was surprised that one of our developers didn't know that SELECT doesn't necessarily return data in order it was inserted in a table (say by PK).  But the question is "why is this the case"? Does the sql standard simply not specify it as required behavior? It is clear that the MS sql engine will, in practice, often return the data in the insert order, giving the impression that that is the norm.

     

    TIA,

    Barkingdog

    P.S. It would be really cool if anyone could provide a data "insert" query where the SELECT results do NOT follow the order of the inserted data.

     

     

    • Edited by edm2 Friday, January 14, 2011 7:14 PM edit
    Friday, January 14, 2011 7:12 PM

Answers

  • Hi barkingdog,

    I was surprised that one of our developers didn't know that SELECT doesn't necessarily return data in order it was inserted in a table (say by PK).  But the question is "why is this the case"? Does the sql standard simply not specify it as required behavior?

    Exactly. The relational model defines a table in a relational
    database as an *un*ordered set of rows.

    It is clear that the MS sql engine will, in practice, often return the data in the insert order, giving the impression that that is the norm.

    This is not true. In practice, rows will often (but not always!) be
    returned in order of the clustered index (often the primary key), or
    in order of an unclustered index that happens to cover all columns
    used in the query.

    P.S. It would be really cool if anyone could provide a data "insert" query where the SELECT results do NOT follow the order of the inserted data.

    Sure.
    http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
    http://sqlblog.com/blogs/hugo_kornelis/archive/2007/01/16/Beatles_2D00_vs_2D00_Stones_2D00_explanation.aspx


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Naomi N Sunday, January 16, 2011 4:31 AM
    • Marked as answer by Alex Feng (SQL) Monday, January 24, 2011 10:33 AM
    Friday, January 14, 2011 11:25 PM
  • This can also change based on the SQL Server edition in some cases.

    Enterprise edition has an optimization known as "Merry-go-round" scan or Advanced scanning. Say if an SP is scanning a huge table and a second execution of the same SP is started before the first one completes, Enterprise edition can share the data from the first invocation and fetch the remaining data from disk. This can be a huge benefit on datawarehouse scenarios. In this case, data is sent in a different order unless it is explicitly ordered using the ORDER BY.

    http://msdn.microsoft.com/en-us/library/ms191475.aspx


    http://SankarReddy.com/
    Saturday, January 15, 2011 12:12 AM

All replies

  • Yes, the SQL standard says default results are unordered - unless you ask for them to be ordered!

    Is that so hard to do, or to understand? :)

    Any query can be unordered, even "select * from foo".  It does not depend on the insert order, though I suppose we could try to produce a repro script that would mostly demonstrate things.

    Results may even be ordered at one execution and differently ordered on each subsequent call.

    Josh

     

    Friday, January 14, 2011 11:15 PM
  • Hi barkingdog,

    I was surprised that one of our developers didn't know that SELECT doesn't necessarily return data in order it was inserted in a table (say by PK).  But the question is "why is this the case"? Does the sql standard simply not specify it as required behavior?

    Exactly. The relational model defines a table in a relational
    database as an *un*ordered set of rows.

    It is clear that the MS sql engine will, in practice, often return the data in the insert order, giving the impression that that is the norm.

    This is not true. In practice, rows will often (but not always!) be
    returned in order of the clustered index (often the primary key), or
    in order of an unclustered index that happens to cover all columns
    used in the query.

    P.S. It would be really cool if anyone could provide a data "insert" query where the SELECT results do NOT follow the order of the inserted data.

    Sure.
    http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
    http://sqlblog.com/blogs/hugo_kornelis/archive/2007/01/16/Beatles_2D00_vs_2D00_Stones_2D00_explanation.aspx


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    • Proposed as answer by Naomi N Sunday, January 16, 2011 4:31 AM
    • Marked as answer by Alex Feng (SQL) Monday, January 24, 2011 10:33 AM
    Friday, January 14, 2011 11:25 PM
  • This can also change based on the SQL Server edition in some cases.

    Enterprise edition has an optimization known as "Merry-go-round" scan or Advanced scanning. Say if an SP is scanning a huge table and a second execution of the same SP is started before the first one completes, Enterprise edition can share the data from the first invocation and fetch the remaining data from disk. This can be a huge benefit on datawarehouse scenarios. In this case, data is sent in a different order unless it is explicitly ordered using the ORDER BY.

    http://msdn.microsoft.com/en-us/library/ms191475.aspx


    http://SankarReddy.com/
    Saturday, January 15, 2011 12:12 AM