Order by with select into

[Paulo Andre Ortega Ribeiro]

I have a Microsoft SQL Server 7.0.

I wrote a sql command that creates a temporary table with a ORDER BY
clause.

When a execute a SELECT on this temporary table sometimes the result is
ok, but sometimes is not ordered. I didn´t see anything like that. Any
clue?

Is there any kind of limits with temporary tables ? Because the command
that creates the temporary table is working and the rsults is always
ordered. But when I create a table with it, sometimes the table is not
ordered.

Paulo
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Paulo Andre Ortega Ribeiro" <an*******@devdex.com> wrote in message
news:3f*********************@news.frii.net...

I have a Microsoft SQL Server 7.0.

I wrote a sql command that creates a temporary table with a ORDER BY
clause.

When a execute a SELECT on this temporary table sometimes the result is
ok, but sometimes is not ordered. I didn´t see anything like that. Any
clue?

Is there any kind of limits with temporary tables ? Because the command
that creates the temporary table is working and the rsults is always
ordered. But when I create a table with it, sometimes the table is not
ordered.

Paulo

Rows in tables (temporary or permanent) never have an order, even if you
used ORDER BY when you did the INSERT, and even if there's a clustered index
on the table. The only way to be sure you get ordered data is to use ORDER
BY when you SELECT it.

Some tables, especially with clustered indexes, may look like the data is
ordered, but you can't assume it will always work. You could think of it
like this - when you SELECT from the table, you create a result set, and
ORDER BY only works on the result set, not on the table.

Is there some specific reason that you want to order data in the table,
instead of using ORDER BY in your queries? If there is, then maybe you could
give some more details about what you are trying to do, and someone might be
able to suggest a different solution.

Simon

[Anith Sen]

Paulo,

Tables do not have an order. In other words, 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
order of rows which you see when you do a SELECT without an ORDER BY clause
is a undefined/arbitrary order chosen by the optimizer based on the physical
characteristics, indexes, access paths, complexity of joins if any, other
statistical information etc. & many undocumented factors. Hence you should
not rely on the 'order' of rows in a table which you see on the screen.

To repeat, rows in a table do not have a logical order. 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 )