Difference in order by clause behaviour in SQL Server 2008 vs 2014

Question

I was using a temp table variable in my stored procedure and was doing some insertion into temp table from another temp table with order by clause. It was working in SQL Server 2008. Order by clause was working while inserting into table.

But now I have upgraded the database to SQL Server 2014 and now order by clause behavior has changed. It no longer inserts the data in ordered manner.

Eg :

    declare @table1 table(id int, datecolumn datetime)
    declare @table2 table(id int, datecolumn datetime)
    declare @table3 table(id int, datecolumn datetime)

    insert into @table1 values(1, getdate());
    insert into @table1 values(1, DATEADD(hour, 1, getdate()));
    insert into @table1 values(1, DATEADD(hour, 2, getdate()));
    insert into @table2 values(2, getdate());
    insert into @table2 values(2, DATEADD(minute, 55, getdate()));
    insert into @table2 values(2, DATEADD(minute, 130, getdate()));

    insert into @table3
       select * 
       from  
          (select * from @table1
           union all 
           select * from @table2) t 
       order by datecolumn

    select * from @table3

And output is correct in SQL Server 2008 which is

id  datecolumn
---------------------------
1   2015-03-31 21:27:48.290
2   2015-03-31 21:27:48.290
2   2015-03-31 22:22:48.290
1   2015-03-31 22:27:48.290
1   2015-03-31 23:27:48.290
2   2015-03-31 23:37:48.290

But in SQL Server 2014, it is showing incorrect

id  datecolumn
----------------------------
1   2015-03-31 10:57:22.920
1   2015-03-31 11:57:22.920
1   2015-03-31 12:57:22.920
2   2015-03-31 10:57:22.920
2   2015-03-31 11:52:22.920
2   2015-03-31 13:07:22.920

How do make it work in SQL Server 2014 ?

If you **want an order**; then you **must** explicitly add an `ORDER BY` clause to your `SELECT` - there **is no** system-inherent ordering - if you don't specify an `ORDER BY`, then SQL Server is free to return the data in whichever way it chooses. Therefore, that SQL Server 2014 behavior is **perfectly fine** and by no means *wrong* or *defective* — marc_s, Mar 31 '15 at 18:43
It might have been "working" **by coincidence** in SQL Server 2008 - but again: *without* an explicit `ORDER BY`, SQL Server (in ***any*** version) does ***not guarantee*** any ordering — marc_s, Mar 31 '15 at 18:44

score 3 · Answer 1 · answered Mar 31 '15 at 16:42

3

You have to have the order by clause in the select. SQL Server does not guarantee that the order of rows stays the same that it was in the insert. Either do it with the original time column or add an identity field.

answered Mar 31 '15 at 16:42

James Z

12,209
10
24
44

SQL Server, like all relational databases, that based on the same data, you will get a set that contains consistent data - but unless you add an 'order by' clause there is no guarantee that any kind of order be maintained. – benjamin moskovits Mar 31 '15 at 16:45
but it is working in SQL 2008 ...then why order clause is not working in SQL 2014 – Mahajan344 Mar 31 '15 at 16:45
2

Because in SQL 2008 -SQL Server, each time that it returned data to you it seemed to be returning it in the order you wanted. You were just lucky. – benjamin moskovits Mar 31 '15 at 16:46
It looks like the 2014 data is in the original order. Maybe SQL Server is now removing the sort since it shouldn't be used there? That should be visible in query plan too if that's the case. – James Z Mar 31 '15 at 17:02

Stephen Turner · Answer 2 · 2015-04-01T08:24:19.620

1

The order data in a SQL table is physically stored is not normally determined by the order it was inserted, but rather by the clustered index on the table. In fact if you don't have a clustered index the order is undefined.

If it wasn't a temp table you could check the design of the table and look for a clustered index, possibly within the primary key for the table.

However you should not rely on the clustered index to provide ordered results in any query. The optimiser might choose a different index to to retrieve the data, for example if there is a smaller covering index that returns the data (see Without ORDER BY, there is no default sort order. and for a lot more detail No Seatbelt - Expecting Order without ORDER BY courtesy of @marc_s)

In short the physical order of data in your table is of interest internally to the optimiser but if you want your results ordered add an ORDER BY clause.

edited Apr 01 '15 at 08:24

answered Mar 31 '15 at 16:44

Stephen Turner

7,125
4
51
68

No, the order is ***NOT*** determined by the clustering index. If you need order - specify an `ORDER BY` - that's the ***only*** real, working solution. See [No Seatbelt - Expecting Order without ORDER BY](http://blogs.msdn.com/b/conor_cunningham_msft/archive/2008/08/27/no-seatbelt-expecting-order-without-order-by.aspx) or [Without ORDER BY, there is no default sort order](http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/05/20/without-order-by-there-is-no-default-sort-order.aspx) that clearly show that the clustering key is ***NOT*** determining the order of your `SELECT` output – marc_s Mar 31 '15 at 18:45
1

Thanks @marc_s I've improved my answer from your comments. – Stephen Turner Apr 01 '15 at 08:10

score -1 · Answer 3 · edited Mar 31 '15 at 18:45

-1

Thanks Guys for your help. From SQL Server 2012 execution of order by has been changed. So now adding primary key will give me same output as I was getting in SQL Server 2008.

So now I have altered the table to

declare @table3 table (Sort_Id INT IDENTITY(1,1) PRIMARY KEY, id int, datecolumn datetime)

And it's working :)

edited Mar 31 '15 at 18:45

marc_s

732,580
175
1,330
1,459

answered Mar 31 '15 at 17:11

Mahajan344

2,492
6
39
90

1

Until it doesn't. Put an order by on the select from @table3, and it will always work. – Shannon Severance Mar 31 '15 at 17:15
1

Yes the order by should definitely be in the select part, not in the insert. – James Z Mar 31 '15 at 17:17
@JamesZ - It needs to be in both. Once so the identity values are assigned in the required order and once to return from the table in that order. – Martin Smith Mar 31 '15 at 17:18
Yes sorry I was too brief, of course with identity it must be in both, but without identity it's enough to be just in the final select -- and that way there's no extra sorting / primary key maintenance required – James Z Mar 31 '15 at 17:23

Difference in order by clause behaviour in SQL Server 2008 vs 2014

3 Answers3