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?
views:
233answers:
3
+5
A:
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).
Marc Gravell
2009-04-02 07:11:42
A:
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.
Cruachan
2009-04-02 10:45:13
The trouble here is that if you insert rows fast enough (which we do in an import), you get duplicate datetimes since the granularity of a datetime is not small enough on SQL Server.
Jon
2009-04-02 13:44:17
Hmm, good point, I'd rather been focusing on the idea that you'd be inserting through normal transactional database operations. OK, the answer then in that case is that identity is in order, but you should still include the timestamp because that will allow you to detect any issues....
Cruachan
2009-04-02 13:56:49
... arising such as identity insets or resets or whatever that would invalidate the assumption.
Cruachan
2009-04-02 13:58:49