All of the following samples are based on the following table:
Find identity column seed and increment values
We can use the IDENT_SEED, IDENT_INCR and IDENT_CURRENT functions to retrieve the identity seed and increment values, as well as the current value. Note that the next row will have IDENT_CURRENT() + IDENT_INCR() as its identity value.
An alternative way is to query the sys.identity_columns system view for the same values. Note that the sys.columns view (of which sys.identity_columns inherit) has an object_id column specifying the object ID of the table to which the column belongs. Thus we’ll have to apply a predicate filtering away any columns not belonging to the desired table, tblCars in this example.
A third way of finding the current identity value is to use the DBCC CHECKIDENT function:
Changing the seed value
Using the DBCC CHECKIDENT command we can manually apply a new seed value to our table. Note that this will enable you to set an identity value that’ll cause the identity column to have duplicates unless you have a unique index on the column, in which case you’ll get an error instead. Thus, if you manually reseed the table, make sure you won’t run into duplicate values.
If for some reason the identity value has become out of synch with the values in the table, we can automatically reseed the table to a valid identity value. In the following case I’ve manually set the seed to 10 while the highest identity value in the table is 27. After running RESEED with no explicit value, the seed is automatically set to 27, thus the next inserted row will have an identity value of 32, provided the increment is 5.
Getting the maximum and minimum identity values
Using the IDENTITYCOL alias for any identity column in a table (of which there can be at most one), we can easily select the maximum and minimum identity values:
Changing the identity increment value
Unfortunately there’s no easy way to change the increment value of an identity column. The only way to do so is to drop the identity column and add a new column with the new increment value. The following code will create a new temporary table, copy the data into it, recreate the original table with the correct increment value and then finally copy the data back using SET IDENTITY_INSERT ON.aspx) to insert explicit values into the identity column.