Identity Column in SQL Server

ali_alannah used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
>1-  Can i change this behaviour ?
no, you cannot change that behavior, and there should be no need trying to do so.

you cannot get the value that would have been generated.

the only "workaround" here would be to have a table with basically only the ID value (and eventually some timestamp/username information along of when/who created the record).
that insert will work anyhow (unless the db was full or otherwise unavailable, but then you have other much bigger issues anyhow ...) , and your actual table insert would need to use that ID value (as foreign key) etc.
but again: not really needed, in 99+% of the cases ...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial