Our Training Programs

All contents are copyright of their authors.

Auto Increment value increase to higher amount in Sql Server

Auto Increment value increase to higher amount in Sql Server

Ajeet Singh

6yAsked Dec 20, 2019, 8:58 AM1.6kSQL

After id ( 10) new value should be 11 but the result is showing 1010

I am having the same issue with many tables in Sql Server Management Studio.

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

It is the same account you read, post and publish with — and you will come straight back to this page.

Jignesh Kumar6y ago
Hi Ajeet,

When table have auto increment column with seed values then this will happen,
Lets insert 100 records in table so your Id columns will look like this,
1,2,3,4,5,.....,98,99,100. Now delete records with Id 98,99,100.
Now insert new values then Id column will be101.
Please refer this link for more understanding,
https://www.c-sharpcorner.com/blogs/how-to-reset-identity-column-values-in-sql-server1
Mageshwaran R6y ago
Hi Ajeet,
Please refer the below link, it might helps you
https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server
Laxmidhar Sahoo6y ago
If the ros deleted that happens Try

Changing the identity seed for the table Employees to 1000:

DBCC checkident ('Employees', reseed, 1000)

The next row inserted will begin at 1001.
Rajanikant Hawaldar6y ago
Hi Ajeet https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database https://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server