What is an UPSERT?
An UPSERT is a technique to perform inserts/updates in a database using the least amount of database reads. For maximum performance, you want to absolutely minimize the number of reads required of the database.
An UPSERT is really just an UPDATE combined with an INSERT. The classic example, is if you are writing a stored proc to look for a row and update it; if it does not exist, then insert. Most programmers will use an IF, EXISTS syntax to accomplish this. This technique will perform two I/O operations regardless of the existence of the row: one to see if the row exists, and a second to either update or insert. A basic UPSERT syntax is:
———————————–
UPDATE dbo.tObject
SET objectStatusCode = ‘Active’
WHERE objectNumber = 100
IF @@ROWCOUNT = 0
INSERT INTO dbo.tObject
(objectNumber,
objectName)
VALUES
(100,
‘Test Object’)
GO
–You can run the following before and after the statement to view I/O stats:
SET STATISTICS IO ON
–Statement here
SET STATISTICS IO OFF
———————————–
If an INSERT is required, then we will have 2 I/O operations, but if only an UPDATE is required, it would only be 1 I/O operation. The alternative with an IF, EXISTS syntax is to always have 2 I/O’s.
