Identity column MS SQL SERVER

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

  1. techlearner

    techlearner New Member

    Joined:
    Apr 6, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    Nope.
    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

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    You posted in Visual Basic section and I have moved it to the correct section.
     
  5. techlearner

    techlearner New Member

    Joined:
    Apr 6, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
    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

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    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

    Joined:
    Apr 6, 2006
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Mar 12, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Feb 9, 2010
    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.mindfiresolutions.com/
    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

    Joined:
    Jun 7, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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

    Joined:
    Aug 16, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    CEO
    Location:
    Chicago, illinois
    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

    Joined:
    Oct 21, 2009
    Messages:
    14
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer
    Home Page:
    http://www.sql-programmers.com
    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
     
  14. mountainman

    mountainman Banned

    Joined:
    Feb 6, 2012
    Messages:
    11
    Likes Received:
    0
    Trophy Points:
    0
    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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice