How to change the identity constraint of exsiting table?

I've a table tEmployee which has column EmpID. EmpID is a Identity column seed as 1 and increment as 1. Now already records are present in the table.

I would like to modify the seed value as 10000000 and increment value as 1. But existing records should not get affected. How to achieve this?

Please do assist
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
Paul JacksonConnect With a Mentor Software EngineerCommented:
You can use the DBCC CheckIdent command to reseed the table identity column, open a query window and use the following command :

DBCC CHECKIDENT(tEmployee, Reseed, 9999999)

the next identity that will be used will then be 10000000.
TempDBAConnect With a Mentor Commented:
This is what happens when you reseed.

The current identity value is set to the new_reseed_value. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use new_reseed_value as the identity. Otherwise, the next row inserted will use new_reseed_value + 1. If the value of new_reseed_value is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.


To check the current Identity value, you can use the following command:
Easwaran ParamasivamAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.