Me and a couple of colleagues are having a discussion regarding the following case:
OrderStatus table we're keeping track of all the statuses an order goes through in time, including
"Returned", etc. There's a timestamp field in the table to keep track of when each status was added. However, there are two statuses that may be added exactly at the same time, one after the other, in which case both may have exactly the same timestamp (including milliseconds). In another part of the code, we need to check which is the last status an order was set to, and since we're ordering by the timestamp we can easily get the wrong record from the database.
The table has an
IDENTITY(1,1) primary key, and my colleagues think we should order using that column, since that will always necessarily give you the latest record added to the table.
I feel like an
IDENTITY column should be voided of any meaning and thus should not be used for any sorting, and would rather do a
Thread.Sleep(1) to make sure the timestamps are different and we can keep sorting using that field.
What do you think is the best practice in this case?