Is there a way to reset the IDENTIY column in sql server? and would this be a reason not to use an Identity Column?

Question

I have a development database that has fees in it. It has a feeid, which is a unique key that is the identifier. The problem I run into is that the feeid/fee amount may not match when putting updating the table on a production server. This obviously could lead to some bad things happening, like overcharging for something or undercharging. Is there a way to match reset identities in sql server or match them or is this an example of when you would not want to use them?

Answer 1

You can set IDENTITIY INSERT ON, update the IDs (make sure there are no conflicts) and then turn it back off.

Answer 2

That is the behaviour of an identity column, this is also what makes it so fast because it doesn't lock the table

to reset an identity either use DBCC CHECKIDENT or TRUNCATE TABLE

to insert IDs from one table to another and to keep the same values you need to do

SET IDENTITIY_INSERT ON

--upddate/insert rows

SET IDENTITIY_INSERT OFF

keep in mind that during the time between the two SET IDENTITIY_INSERT statements that your regular inserts will FAIL!

Answer 3

1. Don't make your primary keys "mean something" other than identifying an unique record. If you need to hard code an ID somewhere, create another column for it. So-called "natural keys" are more trouble than they're worth
2. If, for some reason, you decide that either you will not or cannot follow the first rule, don't use any automatically generated key values.