Identity column MS SQL SERVER

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.

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?

Nope.

Quote:

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.

No way you can use that number.

Quote:

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.

Delete the column. Insert the column back again when you are ready with the implementation.

Thank you very much for your replies. I'm sorry Shabbir for the inconvinience, I didnt find the thread in VB, so I posted it again , sorry about that.

Coderzone, I got your point about identity column and relationships. My whole VB code is based on the sql database, so should I continue coding till release time, and reset the identity and corresponding relationships then. What about the changes and hence more testing that I might have to do after that.

Thank you very much for your replies. I'm sorry Shabbir for the inconvinience, I didnt find the thread in VB, so I posted it again , sorry about that.

Yes thats OK. I have closed that thread as duplicate.

Quote:

Originally Posted by techlearner

Iam sorry about asking so many silly questions, but I am a newbie.

You dont need to be sorry for that and just go ahead and ask whatever you have to but remember that if there is some one else requiring help where you have the expertise then go ahead and help him out.

Yes you should continue till the release time and then you should drop the column but I would suggest you try it once so that you know exactly how its done and at the time of release you can do that once again as well. Check to see if it works perfectly as desired as well.

Lets have an Employee table in which some employee records are saved with the unique identity numbers for each employee. Now, suppose some records were deleted from the Employee table due to some reasons and now I want all the deleted records from Employee table,which may not be possible now as records are deleted from the table,but we can get the list of Unique Identity records deleted from Employee table by using query.

To demonstrate this first of all we have to create an Employee Table with some records inserted to it.Then we can delete some selected records from the Employee table to verify the output.