Insert into # Select without order by

Question

Insert into # Select without order by

sqlguy80

SSCrazy

Points: 2765
More actions
February 28, 2017 at 7:36 pm

#319155

Hi,
We upgraded the code base from SQL Server 2008 R2 to SQL Server 2014 and noticed that the piece of old legacy code where they insert into # tablename
and then later select * from # WITHOUT ORDER BY clause, the result set and format is different than what is expected.
Any quick to fix this for over 300 sprocs and 500 databases?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004430 More actions · Answer 1

You have to be more specific as to what you expected and how those expectations weren't met. If you're expecting a "natural order" of some sort in the table, then you shouldn't have expected that even in 2008. If it occurred, you just got lucky.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

No. If you want the data out of those temp tables in a particular order, you need an ORDER BY on the select that retrieves from the temp table.

Tables are unordered sets of rows, SQL can and will return rows from a table in any order it likes. AFAIK, order by on inserts have been ignored since SQL 2000 or before, unless there's an identity column on the table, and even then the order by is only for assigning the identity.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass