Geek Answers Handbook

Is it wrong to use an identifier column to determine the order in which rows are created?

Possible duplicate:
Can I use a SQL Server identifier column to determine the inserted row order?

If the identifier column is redistributed, it cannot be used to determine the row insertion order, but I have no reason to constantly update the identity.

Are there reasons why I should not use the identifier column to determine the order of creation?

+4
source share
2 answers

This is not considered good practice. For example, two processes that perform inserts in a table with simultaneous transactions may, on some servers, have pieces of identifiers assigned to them, so any row inserted from one transaction will have a lower id than any row inserted from another transaction. In addition, this can sometimes cause spaces in the sequence of identifiers. And there may be other scenarios that may happen unexpectedly.

In short, auto-incrementing identifiers are not always guaranteed to be continuous and upstream.

+1
source

Because it would be unreliable, so I would not use it. You can have two processes requesting identifier values ​​at the same time, and process 1 got the first value, and process 2 got the second value, but process 2 actually completed the transaction earlier and thus was inserted earlier. The date and time field for the inserted date is the only reliable choice if you want to know the order in which the records were actually inserted.

+2
source

More articles:


All Articles