Wrong 'Order By'

[jfsagae]

Does anyone know any bug relationed with memory paging and order by clause?

I try to explain:

I have the following code inside a SP:

create table #tempA (colA char(2), colB numeric(7), colC numeric(3))

create table #tempB (colA char(2), colB numeric(7), colC numeric(3))
)
insert into #tempA
(...)

(...)
insert into #tempB (colA,colB,colC)
select colA,colB,colC
from #tempA
order by
colB,colA,colC

select * from #tempB

While using the reserved memory for the SQL Server, the last 'select' works pretty well. After the memory swapping starts, the 'select' returns data in incorrect order.

Any Ideas?
Thank you for all support than you can give me.

[skhanal]

Without an ORDER BY clause in your SELECT statement, the order is not guaranteed. That is because SELECT * may be done in parallel using multiple threads and result will not be sorted because you did not ask for it.

[jfsagae]

Thank you very much! I'd never had been faced with this case before.

I've corrected the code. It worked very well. Living and learning!