Last post Aug 16, 2019 11:31 AM by PatriceSc
Aug 14, 2019 07:26 AM|aabedeni056|LINK
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?
Aug 14, 2019 08:02 AM|AddWeb Solution|LINK
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.
Aug 14, 2019 09:12 AM|Mikesdotnetting|LINK
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
Aug 14, 2019 09:21 AM|aabedeni056|LINK
id :data type:integer
my id full as automatic not manual.
i dont never face duplicate .
my id jump 1034 after 215.
Aug 14, 2019 10:09 AM|AddWeb Solution|LINK
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.
Aug 14, 2019 12:23 PM|PatriceSc|LINK
Identity values are cached and not guaranteed to be provided without any gap. See
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.
Aug 15, 2019 04:05 AM|aabedeni056|LINK
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?
Aug 16, 2019 11:31 AM|PatriceSc|LINK
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...