One day I wanted to set the identity column of a newly created table to a higher number, so I issued the following SQL command:
dbcc checkident('table_name', reseed, 3000)
Then after inserting a few rows, I noticed the numbers in the identity column was still incrementing from 1 (original seed value).
I realised what’s wrong after looking up the function in Books Online:
You cannot use DBCC CHECKIDENT to perform the following tasks:
- Change the original seed value that was specified for an identity column when the table or view was created.