In addition to deleting and inserting test data, we need to call
Update: TRUNCATE did it.
I'll go for the bonus point: sa_reset_identity() has a checkpoint as a side effect.
The reason for that? Probably because a checkpoint is required to force the change to actually appear in SYSTABCOL.max_identity, which is what you (probably) want when you call sa_reset_identity().
I am guessing that putting the call inside an event, and using TRIGGER EVENT to fire-and-forget the event multiple times, MIGHT help... but the probability is low, about the same probability as it MIGHT crash the engine having multiple checkpoints in progress at the same time... the highest probability is that it will have NO REAL EFFECT because the engine will single-thread all the checkpoints.
True relief probably requires a change to the engine. TRUNCATE TABLE might do the trick if the tables are empty.
answered 25 Mar '14, 08:18
I'd think it has to do with the fact that sa_reset_identity() does an automatic checkpoint, so you will have a series of checkpoints when doing a series of such reset calls... - cf. the docs:
The need for the implicit checkpoint might have to do with the fact that the system catalog stores the maximum value within the syscolumn.max_identity (or systabcol for SA 10 and above) columns, and these are only modified (like other catalog metadata) by a checkpoint, as Glenn has explained here:
If that is still correct, then I would suspect there is no faster way - unless you might want to suggest an enhanced sa_reset_identity procedure that could reset several tables in one go (i.e. with only one checkpoint call)...