I need help with the identity columns of my tables. I have used the IDENTITY column with increment 1 as primary key for a lot of my columns because of the simplicity that it provides. Now I have the following problems or questions realted to it:-
1. My first problem is that whenver there an insert attempt fails due to any cause like invalidation etc, the identity column still gets incremented. Is there any way to avoid or optimize it so it only increments when and only when a record is inserted?
2. Whenever a record is deleted, is it possible that the identity uses the value of the deleted record when a new insert is made? In other words, it starts from the identity number of the deleted record since it doesnt exist any more and not skip the number.
3. Is there a way to reset the identity column to start from 0 or 1. The problem is that when I test my application, i insert or delete a lot of records to perform different tests.But I want the identity columns to be reset once the software is ready to be used real time.
Thanks in advance