IDENTITY_INSERT, is it a good idea?

Hi,

Today I discovered this command completely by accident and thought that ther are several places which we could use it in our apps.

Talking with a colleague, he is not to sure as it new to him too.

By using this to recover lost identity values, would this have any possible adverse effects on the table, indexes etc.

I can see potential problems when constraints are set between tables/keys. Anyone with any experience using this good and bad would be useful to hear.

Thanks

Adam

One "gotcha" that can occur is that SET IDENTITY_INSERT requires the DDL_ADMIN privilege; this may not be a privilege that you want to give to rank-and-file users.|||

I have primarily used IDENTITY INSERT for DATA Import / Export jobs where i need to preserve the existing values of ID column and I do not want sql server to generate and I also want sql server to let me insert my own values in indentity columns.

|||

You can look at whether or not it is a good idea in two ways.

Technically: No problem whatsoever. If you know the value that was skipped and want to put a value there, it will not harm anything. All the identity property controls is a counter that adds some value to the previous value you used.

Logically: Bad idea. You should use identity valued columns as if you don't know, don't care the value. It would be better if they used a random number in some ways (though that would stink for building clustered indexes :) The idea behind using an identity value is as a surrogate for some value (as a key). Kind of like identifying you by your employee number. If you are employee 10 or employee 20 it will not change your pay. Either could be the CEO or the guy who sweeps up after the CEO.

So use identity_insert to load a table with existing values is perfectly acceptable, but once loaded, if you can accept gaps it is perfect. If not, then generate your own sequence values, looking for gaps.