• Shaarli
  • Tag cloud
  • Picture wall
  • Daily
  • RSS
  • Login
4252 shaares
Filters

Please stop using this UPSERT anti-pattern - SQLPerformance.com

QRCode

Locating the row to confirm it exists, only to have to locate it again in order to update it, is doing twice the work for nothing.

BEGIN TRANSACTION;

UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END

COMMIT TRANSACTION;
https://sqlperformance.com/2020/09/locking/upsert-anti-pattern
September 8, 2020 at 2:57:38 PM EDT *
mssql sql
FILLER
Shaarli · The personal, minimalist, super fast, database-free, bookmarking service by the Shaarli community · Documentation
Fold Fold all Expand Expand all Are you sure you want to delete this link? Are you sure you want to delete this tag? The personal, minimalist, super fast, database-free, bookmarking service by the Shaarli community