0

Today I just discover, at least what I see, as A major flaw in the way SQL Server Operates. I was under the impression that SQL Implicitly Orders by FIFO when Executing Queries with out explicit ORDER BY Clauses. Naturally in most cases this resolves to order by ID (because generally ID's are Given Sequentially). However Today I discovered this is not true when I executed this code

var BadQuery = Model.Titles.Where(t => t.ID > 19632)
                          .Select(i => new { i.ID, i.ExtTitleID })
                          .Take(10).ToList();

//BadQuery Results
//ID: 121148 ExtTitleID: 0001234
//ID: 132638 ExtTitleID: 000263
//ID: 132639 ExtTitleID: 000360
//ID: 132640 ExtTitleID: 000379
//ID: 132641 ExtTitleID: 000387
//ID: 132642 ExtTitleID: 000409
//ID: 132643 ExtTitleID: 000417
//ID: 134246 ExtTitleID: 000514
//ID: 132644 ExtTitleID: 000522
//ID: 132645 ExtTitleID: 000530

var GoodQuery = Model.Titles.Where(t => t.ID > 19632)
                            .Take(10).ToList()
                            .Select(i => new { i.ID, i.ExtTitleID }).ToList();

//GoodQuery Results
//ID: 19633 ExtTitleID: 30706
//ID: 19634 ExtTitleID: 30708
//ID: 19635 ExtTitleID: 30709
//ID: 19636 ExtTitleID: 3071
//ID: 19637 ExtTitleID: 30710
//ID: 19638 ExtTitleID: 30711
//ID: 19639 ExtTitleID: 30712
//ID: 19640 ExtTitleID: 30714
//ID: 19641 ExtTitleID: 30719
//ID: 19642 ExtTitleID: 3072

The Bad Query Is being ordered by the ExtTitleID, which is a varchar in the database. This is not what I expected. While The Good Query Orders Sequentially by ID or FIFO.

It seems to only order by ExtTitleID if I add a select statement in the Query before. The Good Query ToList before it's select meaning the select is occuring in Linq to Object (c#) instead of LinqToEntities(SQL)

My question is what are the specifications for how SQL determines order? Also, Does this anomaly mean I have to refactor and explicitly order anything that has a select statement in the Query?

johnny 5
  • 19,893
  • 50
  • 121
  • 195
  • 1
    If you want your result set ordered in a certain way, use `ORDER BY`. See [this question](http://stackoverflow.com/questions/2040724/how-does-sql-server-sort-your-data). – Joe Farrell Oct 22 '14 at 15:28
  • 1
    The is no order in rows as per SQL specification. You were wrong. – TaW Oct 22 '14 at 15:28
  • Oof, I guess this is why I shouldn't rely on inductive reasoning. Well Now I have to pretty much refactor every Linq call I've ever made... Lesson Learned. – johnny 5 Oct 22 '14 at 15:36

2 Answers2

4

Absent an ORDER BY clause, SQL specifies nothing about the order of result rows. In practice, the order in such cases depends on many factors, including details of the data structure (including indexes), the query, and the data, and implementation of the DBMS.

If you want result rows to be presented in a specific order, then use an appropriate ORDER BY clause.

John Bollinger
  • 160,171
  • 8
  • 81
  • 157
3

http://msdn.microsoft.com/en-gb/library/ms188385.aspx

"The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified."

So there is no default ORDER BY - if you want the order of your results to be predictable, then you need to specify the order by clause.

FJT
  • 1,923
  • 1
  • 15
  • 14
  • Okay, Another Question similar. If I call a Select on a single property does that mean that, my result set is not ordered at all still? – johnny 5 Oct 22 '14 at 16:10
  • 1
    As the MSDN article says, the order cannot be guaranteed. In practice it could well change if an index is added to the table, for example. If you need it ordered, then you must order it. – FJT Oct 22 '14 at 16:22