Ordered Data Inserted into TEMP table, is Read out in Different ORDER??

[RKD]

Im using a query with OrderBy clause to populate a temp table. The Query
works and Order and everything is Fine
But when reading the data out of the temp table, some of the rows are
positioned wrongly??? ie in the wrong order
75% of the ordering seems fine but a few rows are in the wrong places

Insert Into #temptbl
Select col1, col2, col3
From tbltest
Group By col1, col2, col3
Order by col1, col2, col3

Select * from #temptbl



Any Ideas... i thought temp tables will return rows via select statment in
the same order as the original query used to insert the rows into the temp
table???

[Andrew J. Kelly]

You have to use an Order By on the select to guarantee the correct order.
There is no exception to this. The order by on the insert is pretty much
useless in this scenario.

--

Andrew J. Kelly
SQL Server MVP


"RKD" <ragubir_28@hotmail.com> wrote in message
news:2fUOa.13303$La.6351@news-binary.blueyonder.co.uk...

> Im using a query with OrderBy clause to populate a temp table. The Query
> works and Order and everything is Fine
> But when reading the data out of the temp table, some of the rows are
> positioned wrongly??? ie in the wrong order
> 75% of the ordering seems fine but a few rows are in the wrong places
>
> Insert Into #temptbl
> Select col1, col2, col3
> From tbltest
> Group By col1, col2, col3
> Order by col1, col2, col3
>
> Select * from #temptbl
>
>
>
> Any Ideas... i thought temp tables will return rows via select statment in
> the same order as the original query used to insert the rows into the temp
> table???
>
>

[RKD]

But why is this,
isnt the data written into the temp table in the order its inserted?

This is really wierd

Thanks for Replying

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:OCgUnphRDHA.2768@tk2msftngp13.phx.gbl...

> You have to use an Order By on the select to guarantee the correct order.
> There is no exception to this. The order by on the insert is pretty much
> useless in this scenario.
>
> --
>
> Andrew J. Kelly
> SQL Server MVP
>
>
> "RKD" <ragubir_28@hotmail.com> wrote in message
> news:2fUOa.13303$La.6351@news-binary.blueyonder.co.uk...

> > Im using a query with OrderBy clause to populate a temp table. The Query
> > works and Order and everything is Fine
> > But when reading the data out of the temp table, some of the rows are
> > positioned wrongly??? ie in the wrong order
> > 75% of the ordering seems fine but a few rows are in the wrong places
> >
> > Insert Into #temptbl
> > Select col1, col2, col3
> > From tbltest
> > Group By col1, col2, col3
> > Order by col1, col2, col3
> >
> > Select * from #temptbl
> >
> >
> >
> > Any Ideas... i thought temp tables will return rows via select statment

in

> > the same order as the original query used to insert the rows into the

temp

> > table???
> >
> >

>
>

[Anith Sen]

>> But why is this, isnt the data written into the temp table in the order
its inserted? <<

No, tables by definition are logically an unordered set of rows. Using ORDER
BY in a INSERT...SELECT or SELECT...INTO does not mean that the data in the
table is 'ordered'. The only guaranteed way of retrieving rows in a specific
order is to use an ORDER BY clause in your SELECT statement.

--
- Anith
( Please reply to newsgroups only )

[RKD]

Yes, I knew that was the case with permanent tables
but for some reason always thought temp tables were in memory..... so u
could play with the order like u do with lets say ADO RecordSets

Thanks for all the info


"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:eVJqi1hRDHA.1804@TK2MSFTNGP11.phx.gbl...

> >> But why is this, isnt the data written into the temp table in the order

> its inserted? <<
>
> No, tables by definition are logically an unordered set of rows. Using

ORDER

> BY in a INSERT...SELECT or SELECT...INTO does not mean that the data in

the

> table is 'ordered'. The only guaranteed way of retrieving rows in a

specific

> order is to use an ORDER BY clause in your SELECT statement.
>
> --
> - Anith
> ( Please reply to newsgroups only )
>
>

[ajit.singh]

In case of temp table check out if your tempdb is split into more than one and its size. That hurts the order even if you put order by.