Changing Identity starter to a specific value

Question

Hello,

I've got a question about identity seed.

I've got a table as follows:

I want to change identity seed from 1 to 1000. Any solutions?
Thanks for the answer in advance.

seihyung

Answer 1

Hello @c00012 ,

The IDENTITY column can only be set when the table is created, or alter the table add a new IDENTITY column. Actually you cannot alter the existing identity column in a table like other regular columns ( alter table alter column...).

You may need to use the DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE) to update the Identity column value for new records .
This will change the current seed value of the identity column of the specified table.
If you need to insert specific identity values, you can SET IDENTITY_INSERT ON in your insert statement.

In addition, if need to update the Identity column value for existing records, you can drop the IDENTITY column and re-add.(It is recommended to test in the test environment before applying to production. I tested it in a table with 100,000 records and everything is fine).
Or create a new table with IDENTITY(1000,1) then insert data.

Answer 2

Hi,

You can also use DBCC CHECKIDENT to manually set a new current identity value for the identity column.

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver15