Is it bad design to assume an auto-incrementing primary key is time ordered?

Or rather, do the positives outweigh the negatives?

The goal is开发者_开发知识库 to get the N most recent records.

pros:

1. Don't have to index the created_at column
2. Your ORDER BY will be using a clustered index

cons:

1. You're relying on the time ordered nature of the primary key to hold true for the life of the development cycle

Thoughts?

Due to database transactions, multiple sessions may commit at different times, you will find that it is not always time ordered.

Another con by not creating a index on created_on you forgot to mention:

• You cannot search or filter by specific date/time or a range.

I think this question can only be answered depending on the requirements.

Although it is common practice, there are a lot of cases where the primary key become unordered. I've often run into this problem myself. I personally find it best to set created_at column that's set at the time of creation.

Unless you serialize all your inserts there is probably no way to guarantee that the order of the auto-incrementing key is chronological.

If you use a surrogate key to determine the sequence and drive business logic from it then you also undermine the principal advantage of having a surrogate key in the first place. The surrogate is supposed to have no business meaning, so that for example that you can easily update/reallocate the values during database maintenance and schema changes.