--create intotest with clustered index on a
create table intotest (
    a int identity(1,1) not null primary key,
    b int not null)

--populate with random numbers
insert into intotest (b)
select n from numbers order by newid()
go

--create temp table ordered by b
select
    identity(int,-1,-1) as RowNum,
    b
into
    #temp
from
    intotest
order by
    b

--what happened to my ordering?
select top 10 * from #temp order by rownum
go

--try again
drop table intotest
drop table #temp
go

--create intotest, this time with the clustered index on b
create table intotest (
    a int identity(1,1) not null,
    b int not null,
    constraint pk_intotest primary key nonclustered (a))
create clustered index cx_intotest on intotest(b)

insert into intotest (b)
select n from numbers order by newid()
go

select
    identity(int,-1,-1) as RowNum,
    b
into
    #temp
from
    intotest
order by
    b

--now that's right ...
select top 10 * from #temp order by rownum
go

quote:

---

Any ideas how I could rewrite the first select...into so the ordering is correct?

---

Uh, both SELECT...INTO's are the same. Adding the clustered index is what makes it order the way you want. And what's wrong with doing it the way it works now? Is the performance horrendous?

--create temp table ordered by b
select
    identity(int,-1,-1) as RowNum,
    b
into
    #temp
from
    (select top 100 percent
        b
     from
        intotest
     order by
        b) a