SQL Server provides a way to reset the identity column value in a table. This article will show you how to do this in SQL Server. To reset the identity column value in a table, you first need to create a new instance of the table and then use the New Table Wizard to create a new identity column. The following steps will help you do this:

  1. In the New Table Wizard, click on the button to create a new identity column.
  2. In the Name field, type something unique for your table and then click on OK.
  3. In the Type field, select either text or binary data type for your identity column value. You can also choose to have your identity column have an auto-incrementing value or a unique value that is not auto-incremented. Click on OK to create your new identity column and close the New Table Wizard.
  4. To reset your identity column value in your table, use the following steps:

It would be wise to first check what the current identify value is. We can use this command to do so:

For instance, if I wanted to check the next ID value of my orders table, I could use this command:

To set the value of the next ID to be 1000, I can use this command:

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.

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)