<div>
Someone updates the column?<br />
<br />
Daylight Savings time when you lose an hour?
</div>


IT Director

Steve Bohler

<div>
<blockquote>By my understanding, the RecordID field should match the chronological order of the DateStamp field</blockquote>Nope, common misconception. &nbsp;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.<br><br>It's very likely that the primary clustering key on the table should be ( DateStamp, RecordID ) rather than just id. &nbsp;Then you won't have that problem.
</div>


<div>
Thanks Scott. I didn't realize that.<br />
<br />
So, if a record is deleted, SQL may re-use that Identity value?
</div>


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


<div>
&gt;&gt; So, if a record is deleted, SQL may re-use that Identity value? &lt;&lt;<br><br>No, SQL will never do that unless you explicitly reseed the value of the identity. &nbsp;As I stated above:<br><em>SQL guarantees that the same identity value will only be assigned <u>once</u>&nbsp;</em><br><br>&gt;&gt;And if I change the primary key to be both fields, that won't alter the RecordID of existing records, will it? &lt;&lt;<br><br>No.&nbsp; SQL doesn't allow an identity value to be updated, ever.
</div>


<div>
Thanks Scott.<br />
<br />
So, pardon my ignorance. But, how would a record that is inserted EARLIER have a greater identify field value than a record inserted LATER?<br />
<br />
Steve
</div>


<div>
It depends on when each INSERT starts and whether one gets blocked temporarily. &nbsp;it's not common thing, but it can happen.<br>For example, say Transaction A starts doing work, and Tran B starts almost immediately after that.<br>For some reason, Tran A gets blocked. &nbsp;Tran B would continue and do an INSERT(s). &nbsp;Some time after that, Tran A is able to complete. &nbsp;<br><br>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.&nbsp; Thus, you can <em>never</em> rely on identity column for datetime sequencing. &nbsp;<br><br>Again, the proper way to sequence such rows is to <em>cluster</em> the table on ( RelevantDateTime, RecordID ) then use ORDER BY RelevantDateTime [, RecordID], which <em>will</em> 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 <em>big</em> overhead.
</div>


<div>
That makes sense. But I have some Identity values that are really far off from what I'd expect. &nbsp;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.<br />
<br />
<br />
<a href="https://filedb.experts-exchange.com/incoming/2020/04_w17/1450649/Capture.GIF" target="_blank" title="Capture.GIF (13 KB)"><img alt="Capture.GIF" class="file-block lazyload" style="max-width: 344px;" data-src="https://filedb.experts-exchange.com/incoming/2020/04_w17/1450649/Capture.GIF" /></a>
</div>


Capture.GIF

<div>
Yeah, for that type of gap, I think that row 98 must have been inserted later than the other rows. &nbsp;That particular UserID and MessageID must have been delayed getting inserted with that "DateOnFile" for whatever reason: user delay, SQL delay, whatever. &nbsp;There's always some very remote change that someone explicitly inserted RecordID 98 later on with an earlier DateOnFile.<br><br>You really cannot rely on identity to always be sequential. &nbsp;For example, we have data from multiple clients being inserted into a table simultaneously. &nbsp;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.. &nbsp;<br><br>As always with a relational db structure, if you need the rows in a certain order you must explicitly specify that order.
</div>


<div>
<div class="content wysiwyg-content">
Hello,<br />
<br />
Running SQL Server and have a table with several fields, including a RecordID (identity) primary key field, some other info, and a DateStamp field.<br />
<br />
The RecordID field is set to autoincrement by 1.<br />
The DateStamp field is given a default value of the current date/time (getdate())<br />
<br />
I have some examples where, if I query the table by order of RecordID, it doesn't match always the order of the DateStamp field. By my understanding, the RecordID field should match the chronological order of the DateStamp field, as the RecordID field increments by 1 for each new record.<br />
<br />
Are there any known situations where this might occur?
</div>
</div>


Hello,

Running SQL Server and have a table with several fields, including a RecordID (identity) primary key field, some other info, and a DateStamp field.

The RecordID field is set to autoincrement by 1.
The DateStamp field is given a default value of the current date/time (getdate())

I have some examples where, if I query the table by order of RecordID, it doesn't match always the order of the DateStamp field. By my understanding, the RecordID field should match the chronological order of the DateStamp field, as the RecordID field increments by 1 for each new record.

Are there any known situations where this might occur?

SQL Server Identify field order issue

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.