Author, Web Developer

Wayne Barron

<div>
You should not care about the identity values at all... They are intended to ensure unique numbering but not consecutive numbering. Unique numbering should be used internally, e.g. to generate surrogate primary keys which are not presented to users. <br><br>If you need consecutive numbering which is visible to users then you should use your own algorithms based on &nbsp;the retrieving the last used number and generating a new value in a transaction. Of course, you have to handle possible deletions...
</div>


<div>
The 1000, was what Andrei said.<br />
<br />
I just wanted to support pcelba mentioned:<br />
Even if you disable caching, you will still not be guaranteed sequential. &nbsp;Transactions can fail or manually get rolled back. &nbsp;That will create a 'gap' in the identity column.<br />
<br />
Even if you roll your own, you need to account for possible rollback situations. &nbsp;This 'gapless' type of code must lock the table until the transaction commits. &nbsp;If this is an active table in an active system, I hope you can see the problems this can cause.
</div>


<div>
<b>pcelba</b><br />
A few years ago, I did something similar to what you have mentioned and do not think about doing it to this setup. I think &nbsp;I just forgot about doing it.<br />
<br />
I will do as you suggested <b>Andrei</b>.<br />
Thank you.<br />
<br />
And, <b>Netminder</b>, thanks for your informative information as always.<br />
<br />
Wayne
</div>


<div>
<div class="content wysiwyg-content fr-view">
<p>Hello All.<br><strong>SQL Server 2019</strong><br>What would cause the Identity number to go from?<br>(Real live examples)<br><span style=" text-align: left;">------------</span> Example 1</p><table><tbody><tr><td>122</td></tr><tr><td>1089</td></tr><tr><td>1090</td></tr><tr><td>1091</td></tr></tbody></table><p><br>------------ Example 2<br><br></p><table><tbody><tr><td>78</td></tr><tr><td>1044</td></tr><tr><td>1045</td></tr><tr><td>1046</td></tr><tr><td>1047</td></tr><tr><td>1048</td></tr><tr><td>1049</td></tr><tr><td>1050</td></tr></tbody></table><p><br>I have one Identity that starts at a higher number, and it is specified in the<br>Identity Increment = 1<br>Identity Seed = 101<br><br>But none of the other tables have this behavior set.<br>Everything was working perfectly until today when I checked the records and saw that huge number jump.<br><br>Any idea's on this would be very helpful.<br>Wayne</p>
</div>
</div>



Hello All.
SQL Server 2019
What would cause the Identity number to go from?
(Real live examples)
------------
Example 1
122
1089
1090
1091
------------ Example 2
78
1044
1045
1046
1047
1048
1049
1050
I have one Identity that starts at a higher number, and it is specified in the
Identity Increment = 1
Identity Seed = 101
But none of the other tables have this behavior set.
Everything was working perfectly until today when I checked the records and saw that huge number jump.
Any idea's on this would be very helpful.
Wayne

SQL Server Identity Numbers changed to thousands

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.

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.