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

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?

Answer 1

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.

Thanks

Answer 2

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

Answer 3

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

Answer 4

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. 

Thanks.

Answer 5

User753101303 posted

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.

Answer 6

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?

Answer 7

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...