locked
Identity Field RRS feed

  • Question

  • User-797751191 posted

    Hi

      I have Identity field in Sql. There are missing values not in sequence . What is the reason

    Thanks

    Monday, July 22, 2019 4:39 AM

Answers

  • User-719153870 posted

    Hi jsshivalik,

    Generally, If you want to change the value of a field, you should not set it to Identity field.

    Because Identity field uses self-growth to ensure data uniqueness, once a self-growth value has been used, the field should not reappear this value.

    Of course, SQL also provides several ways to reset the value of Identity field.

    1. As @AddWeb Solution says, "set identity_insert TabelName on" can be used to re-insert a value of an Identity field that has been used.

         2. You can also use "dbcc checkident (TableName, reseed, number)" to reset the self-growth initial value of Identity field. 

    Please refer to below codes in SQL:

    ---------------------------first we create a table Items and put some data
    create table Items
    (
    IID int identity(1,1),
    IName varchar(50),
    IQty int
    )
    
    insert into Items values('Item1',12)
    insert into Items values('Item2',13)
    insert into Items values('Item3',14)
    insert into Items values('Item4',15)
    insert into Items values('Item5',16)
    insert into Items values('Item6',17)
    
    select * from Items

    --------------------------Then we delete data with IID=4
    delete from Items where IQty=15
    select * from Items

    --------------------------here we use set identity_insert Items on to add back the data with IID=4
    set identity_insert Items on
    insert into Items(IID,IName,IQty) values(4,'Item4',15)
    select * from Items
    set identity_insert Items off---remember to set it off

    And after this we can get:

    For the second method:

    -------------------------here we delete data with IID=5 and IID=6
    delete from Items where IQty=16
    delete from Items where IQty=17
    select * from Items

    -------------------------here we set the seed to 5 and try to continue to insert data
    dbcc checkident(Items,reseed,5)
    
    insert into Items values('Item5',16)
    insert into Items values('Item6',17)
    select * from Items order by IID

    The final result is:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 6:00 AM

All replies

  • User-1038772411 posted

    Hello, jsshivalik

    Not completely clear about your question, but as per understanding i am providing you one refrence link may be it will help you fine.

    https://www.mssqltips.com/sqlservertip/5693/insert-missing-sql-server-identity-column-values-using-set-identityinsert-command/

    Thanks.

    Monday, July 22, 2019 5:08 AM
  • User1120430333 posted

    Once a count value has been selected from the MS SQL Server DB engine  for an Identity counter, it's used and can't be rolled back. So if for some reason the add of a record using an Identity counter is not successful, the count has been used and it's not going back to its prior count to be used again. If the count number is selected, then a + 1 is added to the count and it's the new number waiting to be used. 

    Monday, July 22, 2019 5:12 AM
  • User-797751191 posted

    Hi Da924

      Numbers are like this 1,2,3,4,18,21 & so on . Can't i reset & start in sequence . 

    Thanks

    Monday, July 22, 2019 5:32 AM
  • User-797751191 posted

    Hi 

      Numbers are like this 1,2,3,4,18,21 & so on . Can't i reset & start in sequence . 

    Thanks

    Monday, July 22, 2019 5:32 AM
  • User1120430333 posted

    Hi Da924

      Numbers are like this 1,2,3,4,18,21 & so on . Can't i reset & start in sequence . 

    Thanks

    You can Truncate the table of all records. You Truncate the table,  and the Identity counter for the table is set back to  starting sequence number. Hopefully, the column in question is not a primary-key column for the table that is using Identity, and you're  not wanting to keep existing records that have the primary-key Identity used, becuase you will have duplicate key exception if a counter number is already in use for a primary-key on a record in the table if new record is added. 

    Monday, July 22, 2019 5:50 AM
  • User409696431 posted

    The identity field is associated with the actual record.  If you delete records, those identity numbers will be gone and you'll have gaps in the sequence.  This is normal and expected behavior.

    Re-creating the table with a sequence with no gaps will be a stop-gap measure if you ever intend to delete records from the table again.

    So: What is the need to have no missing numbers in the sequence?  If that is a requirement, you shouldn't be using an auto-increment identity field.

    Monday, July 22, 2019 6:57 PM
  • User-719153870 posted

    Hi jsshivalik,

    Generally, If you want to change the value of a field, you should not set it to Identity field.

    Because Identity field uses self-growth to ensure data uniqueness, once a self-growth value has been used, the field should not reappear this value.

    Of course, SQL also provides several ways to reset the value of Identity field.

    1. As @AddWeb Solution says, "set identity_insert TabelName on" can be used to re-insert a value of an Identity field that has been used.

         2. You can also use "dbcc checkident (TableName, reseed, number)" to reset the self-growth initial value of Identity field. 

    Please refer to below codes in SQL:

    ---------------------------first we create a table Items and put some data
    create table Items
    (
    IID int identity(1,1),
    IName varchar(50),
    IQty int
    )
    
    insert into Items values('Item1',12)
    insert into Items values('Item2',13)
    insert into Items values('Item3',14)
    insert into Items values('Item4',15)
    insert into Items values('Item5',16)
    insert into Items values('Item6',17)
    
    select * from Items

    --------------------------Then we delete data with IID=4
    delete from Items where IQty=15
    select * from Items

    --------------------------here we use set identity_insert Items on to add back the data with IID=4
    set identity_insert Items on
    insert into Items(IID,IName,IQty) values(4,'Item4',15)
    select * from Items
    set identity_insert Items off---remember to set it off

    And after this we can get:

    For the second method:

    -------------------------here we delete data with IID=5 and IID=6
    delete from Items where IQty=16
    delete from Items where IQty=17
    select * from Items

    -------------------------here we set the seed to 5 and try to continue to insert data
    dbcc checkident(Items,reseed,5)
    
    insert into Items values('Item5',16)
    insert into Items values('Item6',17)
    select * from Items order by IID

    The final result is:

    Best Regard,

    Yang Shen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 23, 2019 6:00 AM