how to set id in table that dont change to 1000? RRS feed

  • Question

  • User1928394251 posted

    hello . i have 1 table  in my sql 2012. when save records in table ،  id change suddenly  and be 1000 after 2000 ....

    i dont know how set it .

    my id isnt correct  in my table .

    the first i reset id in table but it again  repeat.

    please help me .

    i can use triger for id or not ?

    if can ,how write triger for prevent from jump?

    Wednesday, August 14, 2019 7:26 AM

All replies

  • User-1038772411 posted

    Hello, aabedeni056

    first of all, not clear what your tables's ID or which datatype or its primary key or not. you set identity or not.?

    if you set primary key and auto increment identity then you never face duplicate id ior changed autometic id issue. kindly please check once.

    or, you were manually add ID from web page than must have to check in backend side like, page id and database id is exists or not. these senerio will work as per your quesion.


    Wednesday, August 14, 2019 8:02 AM
  • User-821857111 posted

    The jump in identity values by 1000 was a "feature" introduced in SQL Server 2012. This article explains it and suggests some ways to prevent it: https://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is

    Wednesday, August 14, 2019 9:12 AM
  • User1928394251 posted

    id :data type:integer

    is identity:yes

    identity increament:1

    identity seed:1

    is primary

    allow null:no

    my id full as automatic not manual.

    i dont never face duplicate .

    my id jump 1034 after 215.

    id radif
    187 215 0
    188 1034 0
    189 1035 0
    190 1036 0

    Wednesday, August 14, 2019 9:21 AM
  • User-1038772411 posted

    Hello, aabedeni056

    ok, in your case may be your records(beetween like 215 to 1034) was deleted. try to truncate table and then you will get id as intial number as per autoincrement seed. 


    Wednesday, August 14, 2019 10:09 AM
  • User753101303 posted


    Identity values are cached and not guaranteed to be provided without any gap. See https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017#remarks

    As pointed already it happens in particular when you restart SQL Server. If using SQL Server 2017 you could try https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-2017 (and look for IDENTITY_CACHE). AFAIK you can still have gaps if you rollback a transaction.

    Does it really matter ? My personal preference is to never assume id are consecutive.

    Wednesday, August 14, 2019 12:23 PM
  • User1928394251 posted

    hello. it is matter . because i use value id  in another column for accounting records  in my table.

    how to account rows in my trable?

    Thursday, August 15, 2019 4:05 AM
  • User753101303 posted

    Your code is trying currently to "guess" which value were generated? The common approach is to read back the generated value using SCOPE_IDENTITY() or an OUTPUT clause and then reuse them in foreign keys (EF does this automatically for you). Having gaps should not cause any problem for propagating those ids to "child" tables...

    Friday, August 16, 2019 11:31 AM