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.
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.
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...
Member
36 Points
128 Posts
how to set id in table that dont change to 1000?
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?
Member
700 Points
405 Posts
Re: how to set id in table that dont change to 1000?
Aug 14, 2019 08:02 AM|AddWeb Solution|LINK
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.
Thanks
All-Star
189469 Points
27479 Posts
Moderator
Re: how to set id in table that dont change to 1000?
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
Member
36 Points
128 Posts
Re: how to set id in table that dont change to 1000?
Aug 14, 2019 09:21 AM|aabedeni056|LINK
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.
Member
700 Points
405 Posts
Re: how to set id in table that dont change to 1000?
Aug 14, 2019 10:09 AM|AddWeb Solution|LINK
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.
Thanks.
All-Star
45130 Points
16206 Posts
Re: how to set id in table that dont change to 1000?
Aug 14, 2019 12:23 PM|PatriceSc|LINK
Hi,
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.
Member
36 Points
128 Posts
Re: how to set id in table that dont change to 1000?
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?
All-Star
45130 Points
16206 Posts
Re: how to set id in table that dont change to 1000?
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...