Why does sql server automatically sort the rows of table variables?
I noticed that when writing the sproc values inserted into the table, the variables were in a different order than the insert. Is there a way to disable this automatic sort without adding another column to sort? Ideas that come to mind are an automatic index being generated or some sorting setting ... any ideas?
Visit http://sqlfiddle.com/#!3/e1c06/13 to see 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
source to share
If you want them to be ordered by insertion order, add an auto-incrementing field to your table, and then you can include that in the ORDER BY when you select the data.
SQL Server will otherwise not return your data in a specific order - it may be returning it "sorted" right now, but it may not be so in the future.
By the way, the union query itself actually returns the results, ordered differently than they appear in your statement. This is probably the result of using UNION vs. UNION ALL, since union is different, most likely implies some type. So the result you are getting is indeed the insertion order.
source to share
The short answer is (perhaps) "because the storage engine stores your strings in the unordered heap, and this affects how the rows exit when you don't ORDER BY."
If you don't order ORDER BY in the SELECT query, the sort order is undefined , as per the SQL specification. This is the way it is in every SQL database, be it MySql, Oracle or SQL Server. If the data comes from the table in the expected order, which coincidentally or is most likely a side effect of how the optimizer happened to generate the query, or how the storage engine decided to store the rows physically (which is probably the root cause in this case).
If you add the clustered index to the table in the sort order you want many times, but not always, the table will exit in the expected order. Never rely on this behavior.
source to share