3

I noticed when writing a sproc values inserted into a table variable were in a different order than insertion. Is there a way to disable this auto sorting without adding another column for sorting? Ideas that come to mind are an automatic index being created or some collation setting... any ideas?

Visit http://sqlfiddle.com/#!3/e1c06/13 to see exactly what I mean

declare @tmpTbl table (name varchar(100))
insert into @tmpTbl
select 'mark'
union
select 'frank'
union
select 'sharon'
union
select 'jason'

select * from @tmpTbl
markS
  • 580
  • 5
  • 14
  • 3
    There's never a good reason for code to depend on the arbitrary order of data storage in a table. – mellamokb Jan 23 '13 at 15:49
  • i agree, i just found it odd when testing the output of my script .. if i NEEDED it ordered i would use ORDER BY – markS Jan 23 '13 at 15:54
  • "The relational model specifies that the tuples of a relation have no specific order and that the tuples, in turn, impose no order on the attributes." - [Relational database](http://en.wikipedia.org/wiki/Relational_database) – Tim Lehner Jan 23 '13 at 16:04
  • Possible duplicate of [What is MySQL row order for "SELECT \* FROM table\_name;"?](https://stackoverflow.com/questions/1949641/what-is-mysql-row-order-for-select-from-table-name) – philipxy Jun 05 '19 at 03:25

2 Answers2

5

The short answer is (probably) "because the storage engine is storing your rows in an unordered heap, and that effects how the rows come out when you do not specify ORDER BY."

Unless you supply an ORDER BY in your SELECT query, sort order is undefined, per the SQL spec. That's the way it is in every SQL database, be it MySql, Oracle, or SQL Server. If data comes out of a table in the order you expect, that's by coincidence, or, most likely, a side effect of how the optimizer happened to generate the query, or how the storage engine chose to store the rows physically (as is probably the root cause in this case).

If you add a clustered index to the table in the sort order you want, many times, but not always, the table will come out in the order you expect. Do not ever rely on this behavior.

Dave Markle
  • 95,573
  • 20
  • 147
  • 170
  • sounds like a legitimate idea, but i've tested it over 50 times and each time its coming in order which makes me think its auto sorting and not randomly coming out in order each time – markS Jan 23 '13 at 15:57
  • 3
    I didn't say the sort order was **random**, I said it's **undefined**. There is a world of difference. If you input the same data the same way repeatedly, the storage engine or optimizer will optimize your queries and inserts the same way. That does not mean that it will always be this way. A service pack or SQL upgrade or configuration change could make the order change. **Always use an ORDER BY if you care about the ordering of results**. No exceptions. – Dave Markle Jan 23 '13 at 16:01
3

If you wanted them ordered by the insert order then add an auto-incrementing field your table, and you can then include that in the ORDER BY when you SELECT the data.

SQL Server will otherwise not return your data in a particular order - it may seem to be returning it 'sorted' right now, but that may not be the case in the future.

By the way - the union query itself is actually returning the results ordered differently than they appear in your statement. This is likely the result of using UNION vs. UNION ALL, since union is distinct it likely implies a sort of some type. So, the result you're getting actually IS the insert order.

Kevin Dahl
  • 752
  • 5
  • 11
  • 2
    +1 for `UNION` vs `UNION ALL` and the (probable) underlying implementation affecting the sort order. – Tim Lehner Jan 23 '13 at 16:06
  • haha Tim that makes sense ... Union grabs all rows to remove dups so prob does some sorting to it before inserting into the table, but Union All doesn't care about dups ... I would've accepted this as an answer if it wasn't a comment :-p Thank Guys! – markS Jan 23 '13 at 16:10
  • Well, it wasn't really a comment - but whichever :) – Kevin Dahl Jan 23 '13 at 20:05
  • wow i so didn't see the last part of your answer Kevin lol ... just accepted this answer :-p – markS Jan 31 '13 at 19:33
  • @markS, you may have read it before I edited that in. I didn't figure it out until I tried my own suggestion and it still came out in your original order. – Kevin Dahl Jan 31 '13 at 21:02