Identity Column in SQL Server

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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 ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.