SQL Server Identify field order issue

Someone updates the column?

Daylight Savings time when you lose an hour?

By my understanding, the RecordID field should match the chronological order of the DateStamp field

Nope, common misconception.  SQL only guarantees that the same identity value will only be assigned once, not that an INSERT that starts earlier than another INSERT start will always have all lower identity values.

It's very likely that the primary clustering key on the table should be ( DateStamp, RecordID ) rather than just id.  Then you won't have that problem.

Thanks Scott. I didn't realize that.

So, if a record is deleted, SQL may re-use that Identity value?

And if I change the primary key to be both fields, that won't alter the RecordID of existing records, will it?

Thanks again,

Steve

>> So, if a record is deleted, SQL may re-use that Identity value? <<

No, SQL will never do that unless you explicitly reseed the value of the identity.  As I stated above:
SQL guarantees that the same identity value will only be assigned once 

>>And if I change the primary key to be both fields, that won't alter the RecordID of existing records, will it? <<

No.  SQL doesn't allow an identity value to be updated, ever.

Thanks Scott.

So, pardon my ignorance. But, how would a record that is inserted EARLIER have a greater identify field value than a record inserted LATER?

Steve

It depends on when each INSERT starts and whether one gets blocked temporarily.  it's not common thing, but it can happen.
For example, say Transaction A starts doing work, and Tran B starts almost immediately after that.
For some reason, Tran A gets blocked.  Tran B would continue and do an INSERT(s).  Some time after that, Tran A is able to complete.  

Also, keep in mind that you can SET IDENTITY_INSERT ... ON and an a missing id with ANY time on it ANYWHERE in the identity values where there's a gap.  Thus, you can never rely on identity column for datetime sequencing.  

Again, the proper way to sequence such rows is to cluster the table on ( RelevantDateTime, RecordID ) then use ORDER BY RelevantDateTime [, RecordID], which will insure they are listed in order. Of course you could use an ORDER BY RelevantDateTime even if the table is not clustered that way, but that will require sorts which will be big overhead.

That makes sense. But I have some Identity values that are really far off from what I'd expect.  See the RecordID 98 that is between 4 and 5. This is in a table that gets a record inserted only about 10 times per day.

Yeah, for that type of gap, I think that row 98 must have been inserted later than the other rows.  That particular UserID and MessageID must have been delayed getting inserted with that "DateOnFile" for whatever reason: user delay, SQL delay, whatever.  There's always some very remote change that someone explicitly inserted RecordID 98 later on with an earlier DateOnFile.

You really cannot rely on identity to always be sequential.  For example, we have data from multiple clients being inserted into a table simultaneously.  For each client the rows are sequential by date, but we still get identity values all over the place because of the interaction among the INSERTs, assorted page locks, etc..  

As always with a relational db structure, if you need the rows in a certain order you must explicitly specify that order.

Thank you!