ID in sql server Jumb Incremented to 1000

Hello Mohammad,

Please refer this below links.

https://learn.microsoft.com/en-us/answers/questions/517365/sql-server-id-jump-1000-issue-fixed-in-sql-server
https://stackoverflow.com/questions/14146148/identity-increment-is-jumping-in-sql-server-database

Thanks

Hi MohammadEid,

If you have an existing table and need to reset the IDENTITY property:

1. Check Current Identity:

   DBCC CHECKIDENT ('MyTable', NORESEED);
   

    

2. Reset Identity to a specific value:

   DBCC CHECKIDENT ('MyTable', RESEED, 0);

    

This sets the next ID to start from 1 again. If you need a custom increment logic (e.g., 1, 2, 3, 4), use a custom sequence or handle it programmatically.

Hi MohamedEid,

If the ID column is already set as an IDENTITY, you can truncate the table and reinsert the data. Otherwise, you can configure the column as an IDENTITY following below steps.

• Create a new table with the ID as an IDENTITY column.
• Copy the data from the old table to the new table.
• Drop the old table.
• Rename the new table to the old table's name.

Hi Mohammad, 

ALTER TABLE dbo.ExistingTable
ADD ID INT IDENTITY(1, 1) PRIMARY KEY;

This statement adds a new ID column to ExistingTable with the IDENTITY property starting from 1 and incrementing by 1.