<div>
1) You may execute the INSERT statement in a transaction and the identity value should not change.<br />
<br />
2) You may select MAX value from the table.
</div>


1) You may execute the INSERT statement in a transaction and the identity value should not change.

2) You may select MAX value from the table.

<div>
Sorry, I have to correct myself. Transaction does not affect Identity value and you cannot use it to keep the identity column values consecutive.<br />
<br />
So, the only way it to use your own procedure which will generate &quot;identity&quot; values based on some table containing counters. You'll also need a mechanism to reuse the generated counter value when the insert command fails.
</div>


Sorry, I have to correct myself. Transaction does not affect Identity value and you cannot use it to keep the identity column values consecutive.

So, the only way it to use your own procedure which will generate "identity" values based on some table containing counters. You'll also need a mechanism to reuse the generated counter value when the insert command fails.

<div>
<div class="content wysiwyg-content">
Hello <br />
I have a &nbsp;table with an Identity Column<br />
Create &nbsp;Table T1( &nbsp;Col1 INT Identity Primary Key ,COL2nvarchar(100) ...........)<br />
When the insert statement failed for &nbsp;any reason the Col1 still increasing .....<br />
1- &nbsp;Can i change this behaviour ? I means is there a documented way to prevent the Auto increment IF the insert operation failed ?<br />
2- When i use Scope_IDENTITY() function it Returns &nbsp;the SUCCSSED inserted Identity .. what about if INSERT failed how can i get the autoIncremental number for the failed insert?<br />

</div>
</div>


Hello 
I have a  table with an Identity Column
Create  Table T1(  Col1 INT Identity Primary Key ,COL2nvarchar(100) ...........)
When the insert statement failed for  any reason the Col1 still increasing .....
1-  Can i change this behaviour ? I means is there a documented way to prevent the Auto increment IF the insert operation failed ?
2- When i use Scope_IDENTITY() function it Returns  the SUCCSSED inserted Identity .. what about if INSERT failed how can i get the autoIncremental number for the failed insert?


Identity Column in SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.