Identity column MS SQL SERVER

Hi all!!

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

 

Nope.

No way you can use that number.

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

 

I forgot to mention that if you have any relationship based on the auto_increment column then probably you need to be deleting the relationship first.

 

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.

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

Thanks very much.

 

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.

 

Thankyou very much coderzone for your suggestion. I'll test that beforehand.

Shabbir, I understand your point. I'll try to be helpful to others whenever I see something in my level and area of expertise. Thanks.

 

hey! you can use the transaction and rollback feature to fail the insert completely, in that case your identity no will not be increased

 

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.

 

its recommended to delete all the records from the table, or it may create a duplicate values, if identity column is not a primary key.

then use DBCC CHECKIDENT as follows to reseed an identity column
let say tran type is the table name I want to reseed to 0, then I will call following command.

dbcc checkident
(
tran_type, reseed,0
)

 

Same problem I have encountered, I need help too... I have checked back the codes but it doesn't work. Did I missed something?

 

The following query only reset the value from last record on wards. but not reset the middle records

DECLARE @max_id BIGINT
SELECT @max_id = MAX(ID) FROM tblnew
DBCC CHECKIDENT (tblnew,RESEED, @max_id)

You need to table identity column reset then run the following query. It can delete the all records from table and reset the identity column value

TRUNCATE TABLE tblnew
DBCC CHECKIDENT (tblnew,RESEED, 0)

above the case only the ID column is not a primary key

 

As i know that, We can use identity on a primary key column is convenient. If we have other ways of upholding a primary key that is fine, if not even preferable.