Re: how to detect records order in a table with no id, timestamp?

Thomas,

Thanks for the reply.
[Quoted] I know this is true for Oracle, but do you know how this randomness would occor under MS SQL? I just could not generate an incident on MS SQL Server to prove what you said. (I insert and delete on a table hundreds thousands times on MS SQL). Is select implemented differently between Oracle and SQL Server?

Thanks,

In article <346139c6.4077763_at_newshost>, Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>On 3 Nov 1997 23:41:57 GMT, lesliet_at_u.washington.edu (L. Tseng) wrote:
>
>>Is it possible to order an oracle table output when table does not have
>>any id and timestamp info?
>>
>>In MS SQL Server and Sybase, it seems the record just got inserted or updated
>>always comes out the last in select.
>>
>>Thanks for the help in advance,
>>
>>Leslie
>
>
>No, it is not (and even in Sybase and MS SQL Server -- its not). Database
>tables have no implicit 'order' to them. They are managed as heaps. If you
>insert 50 rows, delete 1 and insert another -- you may first the last inserted
>row is the 'first' row or one in the middle or the 'last row'. It is rather
>random.
>
>The behaviour you observed in Sybase and SQL Server on insert (inserting into
>the last page) might not always occur and the update behaviour you saw was
>depending on the fact that most times an update in these databases is actually a
>DELETE followed by an INSERT. In cases where they could do an in place update,
>the row would not move and you would not see this behaviour (so don't count on
>it with them as well -- it doesn't have to happen that way).
>
>If you absolutely need to know the 'order' of records, you'll need to add a
>column and maintain it with a trigger in an before insert or update trigger.
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Bethesda MD
>
>http://govt.us.oracle.com/ -- downloadable utilities
>
>----------------------------------------------------------------------------
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
>NOTICE: In order to cut down on the amount of time I spend applying silly
>logic to email addresses like "In order to cut down on spam, remove THIS
>from my email" or "In order to cut down on spam, reverse my email
>address", etc, I will simply hit delete when that mail bounces back to
>me, which is what I do with spam myself.
>
>I haven't figured out whats more time consuming for me, deleting spam or
>resending emails after re-reading the whole thing to figure out the
>algorithm to apply.

-- 
=-=-=-=-=-=-=-=-=-=-=
Leslie Tseng
lesliet_at_writeme.com
=-=-=-=-=-=-=-=-=-=-=