Questionable SQL practice - Order By id rather than creation time

In typical practice you can almost always assume that an autoincrement id can be sorted to give you the records in creation order (either direction). However, you should note that this is not considered portable in terms of your data. You might move your data to another system where the keys are recreated, but the created_at data is the same.

There is actually a pretty good StackOverflow discussion of this issue.

The basic summary is the first solution, ordering by created_at, is considered best practice. Be sure, however, to properly index the created_at field to give the best performance.

You shouldn't rely on ID for anything other than that it uniquely identifies a row. It's an arbitrary number that only happens to correspond to the order in which the records were created.

Say you have this table

ID  creation_date1   2010-10-252   2010-10-263   2012-03-05

In this case, sorting on ID instead of creation_date works.

Now in the future you realize, oh, whoops, you have to change the creation date of of record ID #2 to 2010-09-17. Your sorts using ID now report the records in the same order:

1   2010-10-252   2010-09-173   2012-03-05

even though with the new date they should be:

2   2010-09-171   2010-10-253   2012-03-05

Short version: Use data columns for the purpose that they were created. Don't rely on side effects of the data.

There are a couple of differences between the two options.

The first is that they can give different results.

The value of created_at might be affected by the time being adjusted on the server but the id column will be unaffected. If the time is adjusted backwards (either manually or automatically by time synchronization software) you could get records that were inserted later but with timestamps that are before records that were inserted earlier. In this case you will get a different order depending on which column you order by. Which order you consider to be "correct" is up to you.

The second is performance. It is likely to be faster to ORDER BY your clustered index.

How the Clustered Index Speeds Up Queries

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads.

By default the clustered key is the primary key, which in your case is presumably the id column. You will probably find that ORDER BY id is slightly faster than ORDER BY created_at.