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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul JacksonSoftware 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.