1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Identity column MS SQL SERVER

Discussion in 'SQL Server' started by techlearner, Apr 19, 2006.

  1. techlearner

    techlearner New Member

    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
  2. coderzone

    coderzone Super Moderator

    No way you can use that number.
    Delete the column. Insert the column back again when you are ready with the implementation.
  3. coderzone

    coderzone Super Moderator

    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.
  4. shabbir

    shabbir Administrator Staff Member

    You posted in Visual Basic section and I have moved it to the correct section.
  5. techlearner

    techlearner New Member

    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.
  6. shabbir

    shabbir Administrator Staff Member

    Yes thats OK. I have closed that thread as duplicate.
    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.
  7. coderzone

    coderzone Super Moderator

    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.
  8. techlearner

    techlearner New Member

    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.
  9. rockpar

    rockpar New Member

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

    elizas New Member

    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.
  11. dubeyniraj2010

    dubeyniraj2010 New Member

    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
  12. junespring001

    junespring001 Banned

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

    sql-programs New Member

    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


    above the case only the ID column is not a primary key
  14. mountainman

    mountainman Banned

    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.

Share This Page