Sort records by time inserted

Question

How can I query data in the order it was created?

I don't have a date-created field in this table.

Answer 1

If you don't have a field storing the time of insertion, or any other meta-data regarding the order of insertion, there is no reliable way to get this information.

You could maybe depend on a clustered index key, but these are not guaranteed. Neither are IDENTITY fields or other auto-generated fields.

To clarify, an IDENTITY field does auto-increment, but...

• You can insert explicit values with IDENTITY_INSERT
• You can reseed and start reusing values
• There is no built-in enforcement of uniqueness for an identity field

If the ID field is your PK, you can probably use that to get a rough idea:

SELECT *
FROM MyTable
ORDER BY IdField ASC

Per your comment, the field is a GUID. In that case, there is no way to return any sort of reliable order since GUIDs are inherently random and non-sequential.

Answer 2

I came across this questions because I was facing the same issue and here is the solution that worked for me.

Alter the table and add an IDENTITY(1,1) column and that identity column will be auto-populated for existing rows when you add it. It will be in the order in which records were inserted.

It worked in my case but not sure if it works in all cases or not.

Answer 3

You can use NEWSEQUENTIALID().