It would be wise to first check what the current identify value is. We can use this
command to do so:
DBCC CHECKIDENT (’tablename’, NORESEED)
For instance, if I wanted to check the next ID value of my orders table,
I could use this command:
DBCC CHECKIDENT (orders, NORESEED)
To set the value of the next ID to be 1000, I can use this command:
DBCC CHECKIDENT (orders, RESEED, 999)
Note that the next value will be whatever you reseed with + 1, so in
this case I set it to 999 so that the next value will be 1000.
**To reset the seed to 1 , then just delete all records from table and execute below
query
DBCC CHECKIDENT (orders, RESEED, 0)
Another thing to note is that you may need to enclose the table
name in single quotes or square brackets if you are referencing by a full path,
or if your table name has spaces in it. (which it really shouldn’t)
DBCC CHECKIDENT ( ‘databasename.dbo.orders’,RESEED, 999)