Identity should only increment on record insertion
sql server 2005: I have an empid column in the employee table with id on.if on error when inserting data into the table. The identification is increasing. i need the identity to increment only if a record is inserted. For example, if I have a generated emp id from 1 to 5, and then on the 6th record insert error ocurrs.and, the next time the insert input value is 7. I want it to be 6.
source to share
Why do you want to do this?
The ID column should only be used as "internal administrative value" for the database, and should not have "business value", so why does it matter that there are spaces in this sequence?
If identity is used correctly, users of your software will never come across an identity column; you just use it to uniquely identify the record.
source to share
I don't think it can be done. If you want your IDs to be exactly consistent, you might have to generate them yourself rather than using the SQL Identity function.
edit: Even rolling back failed transactions will not cause the Identity account to go back down, that's by design, see this other question .
source to share
What valid business reason do you have to care if there are gaps? There is no reason for the database to worry, and for whatever reason, I would like to make sure that identity values are never reused for anything else, as they can cause serious data integrity issues when retrieving information from old reports, etc. Suppose you have a report that shows orders from the last month and then you delete one of the records because the customer is duplicated and thus deduplicated. Then you reuse the identification field for the remote client that was deleted. NOw someone is looking at last month's report to find customer 12345 and the data associated with this cuisotmer belongs to John Smith, not Sally Jones. BUt the person doesn't know because it uses aggreagate,so now she has incorrect information that could have been completely avoided. If she was looking for a delicate client, the process could instead redirect her to the correct client left over from deduplication.
source to share
If you need this particular behavior, you must use stored procedures to generate the identifier. This way you can really rollback. But keep in mind that the current behavior is as intended. Transaction isolation and different levels of reads (dirty reads) will most likely trigger you when you do not use a lock on that id field in your masterdata table that contains the current or next ID value.
source to share