Sql Server Identity Related

Question

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

Nitin Sontakke · 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!

Rafnas T P · Answer

if it is new table and data's are not required please drop that table and recreate it

Ranjit Powar · Answer

Then records are deleted from some query.

Rajesh Kumar · Answer

I have checkd table design mode but i found the same screenshot.

Ranjit Powar · Answer

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.

Rajesh Kumar · Answer

I did not delete any record and I did not define identity (1,1000)

Ranjit Powar · Answer

This happens when you delete records.

Rafnas T P · Answer

hi, may be you have given identity to increment by 1000 as identity(1,1000) or may be you have deleted previous records

Sql Server Identity Related

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (8)