Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   Identity column MS SQL SERVER (http://www.go4expert.com/forums/identity-column-ms-sql-server-t739/)

techlearner 19Apr2006 20:20

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

coderzone 19Apr2006 22:30

Re: Identity column MS SQL SERVER
 
Quote:

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.

coderzone 19Apr2006 22:31

Re: Identity column MS SQL SERVER
 
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.

shabbir 19Apr2006 22:34

Re: Identity column MS SQL SERVER
 
You posted in Visual Basic section and I have moved it to the correct section.

techlearner 20Apr2006 01:44

Re: Identity column MS SQL SERVER
 
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.

shabbir 20Apr2006 07:19

Re: Identity column MS SQL SERVER
 
Quote:

Originally Posted by techlearner
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.

coderzone 20Apr2006 07:27

Re: Identity column MS SQL SERVER
 
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.

techlearner 20Apr2006 17:53

Re: Identity column MS SQL SERVER
 
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.

rockpar 12Mar2009 17:38

Re: Identity column MS SQL SERVER
 
hey! you can use the transaction and rollback feature to fail the insert completely, in that case your identity no will not be increased

elizas 29Mar2010 17:27

Re: Identity column MS SQL SERVER
 
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.


All times are GMT +5.5. The time now is 08:03.