|
|
Posted by drawnai on 10/02/58 11:45
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.
In SQL 2005, use the RowNumber function in a nested sub query.
In SQL 2000, create enough index columns to handle your sort orders.
eg...
Select Mycol1, MyCol2, Mycol3,
MyIDX1 = Convert (Int, Null) ,
MyIDX2 = Convert (Int, Null) ,
MyIDX3 = Convert (Int, Null)
into #myResults
Select blah from whatever order by whatever
contrary to David Portas' assertion this does actually work most of the
time, however, I sure it can't be relied upon, though I've never seen
it fail...
Then, to have multiple order bys, and very very cheap and easy
pagination you then do.
Create Index #IDX1 on #myResults (Mycol1, Mycol2)
Create Index #IDX2 on #myResults (Mycol3, Mycol1)
Create Index #IDX3 on #myResults (Mycol2, Mycol3)
Followed by...
Declare @Fred int
set @Fred = 0
Update M
Set @Fred = MyIDX1 = @Fred + 1
>From #myResults M
With (Index = #IDX1)
set @Fred = 0
Update M
Set @Fred = MyIDX2 = @Fred + 1
>From #myResults M
With (Index = #IDX2)
set @Fred = 0
Update M
Set @Fred = MyIDX3 = @Fred + 1
>From #myResults M
With (Index = #IDX3)
Select *
>From #MyResults
Where IDX Between @X1 And @X2
>
> Any ideas?
Navigation:
[Reply to this message]
|