For one, if SET IDENTITY_INSERT is left ON, we would end up in problems. I have seen this blowing up in the face a couple of times when performed by relatively inexperienced developers, or when they are in a hurry.
I guess the main argument is to modify a table's properties only for the required duration. During BULK operations, if the KEEPIDENTITY is used, the setting takes effect only for the duration of the query, and no more.
commented on Oct 10 2011 11:22AM