CODEWITHSUNDEEP
sql-serveridentity
Jon
Jon

Reputation: 5357

Can I use a SQL Server identity column to determine the inserted order of rows?

I need to be able to determine the order which rows have been inserted into a table (there are no updates). Can I use an identity column to do this? I know that there may be gaps, but are the values guaranteed to be increasing by insertion order?

Upvotes: 7

Views: 622

Answers (3)

Cruachan
Cruachan

Reputation: 15971

As discussed by Marc, yes you can with provisos

What you should do however to definitively fix the problem is add a column

dteInserted datetime not null default getdate()

Then you just select ordered by this.

Myself I automatically add such a column onto any 'data' table in any database I'm designing. Storage is cheap nowadays and timestamping the insertion date on a row is always useful at some point.

Upvotes: 2

Marc Gravell
Marc Gravell

Reputation: 1062512

Largely yes, as long as you don't ever reset it or insert rows with bulk copy, or use IDENTITY_INSERT. And of course assuming that you don't overflow the data-type (which could be impressive).

Upvotes: 7

Ed Harper
Ed Harper

Reputation: 21495

Yes. Any gaps caused by deletions will not be reused

Upvotes: 2

Related Questions