February 26, 2008 at 11:26 am
I have a problem that just started where we insert data into a temp table with an order by then query the temp table depending on the data to be sorted accordingly.
The problem is the data is not always sorted.
What could be causing this.
We added data files to tempdb for each processor and sql was restarted last night.
ex:
insert into #table
select * from table
order by col1
select * from #table
February 26, 2008 at 11:35 am
Order by in an insert have no meaning, and if you don't specify the order in a select, no order is guaranteed.
Rather do it this way.
insert into #table
select * from table
select * from #table
order by col1
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
February 26, 2008 at 12:15 pm
GilaMonster (2/26/2008)
Order by in an insert have no meaning, and if you don't specify the order in a select, no order is guaranteed.
True with one caveat - ORDER BY during an insert would guarantee the values doled out by any Identity field in the destination table. It doesn't guarantee physical order of insert though. Meaning - it will ensure that your identity values will be assigned in order of the ORDER BY, but not the order in which they are actually stored.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2008 at 12:36 pm
Matt Miller (2/26/2008)
True with one caveat - ORDER BY during an insert would guarantee the values doled out by any Identity field in the destination table.
True. I always forget about that. Haven't been using identities for a while.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply