|
|
Posted by David Portas on 11/03/28 11:43
pb648174 wrote:
> 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. In this particular
> instance that I have reproduced we are using SQL 2005 but have also
> seen this on SQL 2000 servers. I had previously asked this question as
> I was using a SELECT INTO statement, but now we are manually creating
> the temp table (Pain in the ass) and still having the same issue. Best
> case scenario is for it to work for a SELECT INTO.
>
> Any ideas?
Tables are NOT logically ordered under any circumstances. You need to
specify ORDER BY here:
Select * from #TempPaging
ORDER BY ...
That is, when you QUERY the table, not when you INSERT.
See the following article for some reliable paging techniques (one
example uses the same method you have so avoid that one):
http://www.aspfaq.com/show.asp?id=2120
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|