|
|
Posted by drawnai on 10/02/40 11:45
Erland Sommarskog wrote:
> 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.
This isn't, though it is guaranteed to work in SQL 2005. afaik.
Creating an index on the chosen columns and then forcing the sliding
update statement
Update M Set @Fred = Col = @Fred + 1 From tabl M with (index=idxname)
is the only way I've ever seen it never fail for multi columns.
>
> Note that this applies to INSERT only - it does *not* apply to SELECT INTO.
That's interesting, I've found the exact opposite, so long as a table
scan is forced on the created table, I can't create temporary indexes
on a temporary table in 2000 if it's created with insert into select,
only with 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
Navigation:
[Reply to this message]
|