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?

pb648174 wrote:
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/m...S,SQL.90).aspx
--

pb648174 (google (AT) webpaul (DOT) net) writes:
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.

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Yes; shot you and replace you with a programmer who has read the first
10 pages of **any** RDBMS book. You are posting realllllllly baaaaad
questions. Please take some time to catch up over the weekend before
you ask us to do your job for you again.

SQL is a set-orient language. Tables -- by definition -- have no
ordering. That is the nature of a set. Have you ever read Dr. Codd's
12 rules for RDBMS/ Look up the Information Principle: all
relationships are shown as values in columns. Ordering is a
relationship, so you need a column(s) for it.

If you do not know who Dr. Codd is or his rules, then you are like a
Geometry student who never heard of Euclid.

Celko,

Give the guy a break. Sheesh.

I'm beginning to think you are just an automated bot that jumps into
every conversation to complain and stir up the fire.

Celko is right.. Big dumb mistake on my part. It does have an identity
column but for some reason I thought it was ordering by that by
default. It's not a misunderstanding of the way the world works, just a
mistake.

pb648174 wrote:
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

set @Fred = 0
Update M
Set @Fred = MyIDX2 = @Fred + 1

set @Fred = 0
Update M
Set @Fred = MyIDX3 = @Fred + 1

Select *

Erland Sommarskog wrote:
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.

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.

(drawnai (AT) hotmail (DOT) com) writes:
This is one more "works most of the time, but don't cry foul if it
doesn't". I see no point of using methods of which the result is
not defined.

Here is a quick example:

select IDENTITY(int, 1, 1) AS ident, OrderID = OrderID + 0,
CustomerID, OrderDate, ShipVia
into Orders
from Northwind..Orders Order by CustomerID, OrderID
go
SELECT * FROM Orders ORDER BY CustomerID, OrderID
go
TRUNCATE TABLE Orders
go
INSERT Orders (OrderID, CustomerID, OrderDate, ShipVia)
select OrderID = OrderID + 0, CustomerID, OrderDate, ShipVia
from Northwind..Orders
Order by CustomerID, OrderID
go
SELECT * FROM Orders ORDER BY CustomerID, OrderID
go
DROP TABLE Orders

It may not be the indexes you were talking about, but the result of a
query should never be dependent of the indexes on the table.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

drawnai (AT) hotmail (DOT) com wrote:
What assertion do you mean? All I said was tables are not ordered. They
are not. The example you posted above does not contradict me because
you specified ORDER BY, unlike the OP who had no ORDER BY in his SELECT
statement (only in the INSERT).

Your second example is more suspect. The behaviour of an assignment in
an UPDATE that references multiple rows is undefined.

--
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/m...S,SQL.90).aspx
--