Sql Server Identity Related

Rajesh Kumar

8y1.4k

I have created stateid as identity but when i insert record it will automattically increased by thousand can anyone tell why this happen. Please see image below

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Please be aware that this behaviour is by design and is well documented. Identity values are now cached by SQL Server from version 2012 onwards.

Please refer to the following article:

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

In particular, read section "Consecutive values after server restart or other failures" under remarks.

Note that for integer data type 1,000 and for bigint data type 10,000 values are cached by SQL Server hence the gap.

Hope it helps!
+2
Rafnas T P8y ago
if it is new table and data's are not required please drop that table and recreate it
+1
Ranjit Powar8y ago
Then records are deleted from some query.
+1
Rajesh Kumar8y ago
I have checkd table design mode but i found the same screenshot.
+1
Ranjit Powar8y ago
Open your table in design mode from SSMS and check identity

Check the Identity increment value.

Another possibility is that if your insert query is in transaction. When error occurs it deletes inserted records.
+1
Rajesh Kumar8y ago
I did not delete any record and I did not define identity (1,1000)
+1
Ranjit Powar8y ago
This happens when you delete records.
+1
Rafnas T P8y ago
hi,
may be you have given identity to increment by 1000
as identity(1,1000)

or

may be you have deleted previous records
+1