Although not recommended, sometimes it is necessary to turn auto-generation of ID numbers off in your database. For instance, I was in a situation where I was completely redesigning a database (deleting tables, renaming tables, purging data, entering new data, ec.). However, the client was insistent on maintaining the ID numbers that were associated with specific items that they wanted to keep.
This is how I turned off the auto-generation of ID numbers in Microsoft SQL Server 2008 Management Studio:
- Right-click on the table name and hit Design
- In the Column Properties tab, find Identity Specification and expand the section
- For (Is Identity) change the dropdown to No
- Hit Save
If you encounter the following error message, you will need to perform an additional step:
Go to Tools > Options and click “Designers”.
Uncheck the “Prevent saving changes that require table re-creation” and hit OK.
Try the above steps again.
When you are done, make sure to turn auto-generation back on!!!
