Using a DELETE
+ INSERT
pattern instead of also involving UPDATE
s can be highly problematic. IMO, it's an anti-pattern. In 99.9% of cases, it's better to use UPDATES
when possible.
Usually the DELETE
+ INSERT
pattern is implemented such that the subset of rows is blindly deleted in the target, which can create a tremendous amount of unnecessary write activity because the data is always re-written even if nothing changed.
Not only does this put load on the I/O subsystem, but it can also kill data access concurrency unless snapshot isolation is used (which will multiply the wasted disk write workload). If the default isolation level is used and the data is concurrently read, this pattern can produce deadlocks very readily and in large quantity.
Having said that, one needs to implement UPDATE
s carefully because they still have the potential to rewrite rows that haven't changed. Thankfully that's solved easily with a bit of extra code. All in all, it's well worth the effort to use UPDATE
s despite the slight added code complexity.
Depending on what you're doing, it may be easier and more reliable to send the entire desired state as a whole to the database using something like a table-valued parameter and sorting things out there instead of on the client side.
DELETE
+INSERT
pattern versus aDELETE
+UPDATE
+INSERT
pattern? – Jon Seigel Mar 6 '14 at 14:29