ORDER BY clause with unknown column name

SQL Apprentice Question
can i use an unknown column in an ORDER BY clause with t-sql?

i know it will always be an identity field and it is in the first column.
it is also the primary key.
can i depend on a recordset always being in this order without the the
clause?


Celko Answers

>> can i use an unknown column in an ORDER BY clause with t-sql? <<


No, you have to sort by something. When do not put the ORDER BY in a
cursor (it is not part of a SELECT, another common newbie assumption),
then the engine can out the rows into a sequence in any order. Every
SQL product will be a bit different, depending on physical storage,
parallelism in the hardware, etc.


>> I know it will always be an identity field and it is in the first column. It is also the primary key. <<


You might want to read a book and find out why IDENTITY can *never* be
a key. By definition. What you are doing is mimicing a 1950's magnetic
tape file in SQL. The IDENTITY is an exposed physical locator you are
using, the same way we used record positions on a mag tape.


>> can I depend on a recordset always being in this order without the [ORDER BY] clause? <<


No. This is the definition of a table -- it is a set without any
physical ordering. When you finally read a book on RDBMS, pay
attention to "The Information Prinicple" and some of the other rules
that Dr. Codd set up.

There are some proprietary kludges you can use to destroy portability
and data integrity. For example, there is a ordinal position number
that was removed from Standard SQL a few years ago, but exists in some
products.


All you will get in Newsgroups are the kludges; you need to get an
education. And it will take you at least a year to do that. Your
whole mindset is wrong and you have to unlearn a lot.