SQL Server Performance

Default Sort Order for Query With No Sort Specified

Discussion in 'General DBA Questions' started by SQLDBA1976, Oct 29, 2008.

  1. SQLDBA1976 New Member

    I have two databases, each having the same table (the table is the same structurally--tables, indexes, constraints, etc.--but could have different data). One particular set of data is exactly the same. When I query that set of data, using a query with no sort parameters, the data is returned in a different sort order in one database than the other.
    I was always under the impression that when no sort parameters are specified, the results will be returned according to the clustered index. For one of my databases, this appears to be true. For the other, it is not. I honestly don't even care about the sort order of the results (if an application or user wants them sorted a certain way, then the sort order should be specified), but it concerns me for a couple of reasons: 1) I'm concerned that there may be some sort of integrity problem with my database. I don't think this is the case, but it worries me a bit. 2) I don't like that I can't explain the difference and want to know why, just for my own knowledge.
    Any ideas?
  2. Adriaan New Member

    Common misconception ...
    Without an ORDER BY clause, the resultset is never in any predictable order.
    You might see pretty predictable ordering without ORDER BY, but you cannot rely on it.
    And with SQL 2005, you can no longer even rely on an ORDER BY clause in a view definition.

Share This Page