Posted by Erland Sommarskog on 06/19/27 11:43

pb648174 (google@webpaul.net) writes:
> I have the following basic statements being executed:
>
> Create a temp table, #TempPaging
>
> Insert Into #TempPaging (Col1, Col2)
> Select Col1, Col2 From SomeOtherTable Order By Col2, Col1
>
> Select * from #TempPaging
>
> I can't provide a reproduceable scenario right now without making this
> into a 200K post, so I'm hoping someone will know what the issue is.. I
> can't find anything regarding this in BOL.
>
> Basically the order is off a little bit every now and then when there
> are large amounts of data, like more than a couple hundred rows. Is
> there something I need to do to guarantee the originally selected
> order?
>
> This is very important that it be in order from the original select
> statement as this is for paging. Adding an order by in the second
> select from the temp table will not fix the problem.

Once the data is in #TempPaging an ORDER BY will result in that
page being ordered. But that does not help if #TempPaging was not
loaded correctly.

> In this particular instance that I have reproduced we are using SQL 2005
> but have also seen this on SQL 2000 servers.

If you are on SQL 2005, the best is to use row_number():

SELECT OrderID, CustomerID, OrderDate, rowno
FROM (SELECT OrderID, CustomerID, OrderDate,
rowno = row_number() OVER
(PARTITION BY 1 ORDER BY CustomerID, OrderID)
FROM Northwind..Orders) AS x
WHERE rowno BETWEEN 100 AND 200
ORDER BY rowno

On SQL 2000 you can use a temp table table with an IDENTITY column,
and insert to that table with ORDER BY. I am told that this is
guaranteed to work, although I seem to recall that David claimed
to have seen conflicting testimony.

Note that this applies to INSERT only - it does *not* apply to SELECT INTO.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]