Order by in a INSERT INTO..SELECT

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 XXX) and still having the same issue. Best
case scenario is for it to work for a SELECT INTO.

Any ideas?

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 XXX) 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
--

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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.