This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with its MERGE statement that will do that in one go we're stuck with 2 ways of achieving this. The biggest problem with every update/insert (upsert for those who haven't heard the term yet) is locking.
Each DML statement basicaly contains 2 locks. IX Lock (Intent Exclusive Lock) and X Lock(Exclusive Lock).
When the SQL Server searches for the data to modify it takes IX lock on the whole page or table and then it takes an X Lock on the rows to be modified.
Our goal here is to minimize anykind of locks. As i said earlier there are two methods of doing this:
1. Update ... if rowcount = 0 insert
2. If row exists update else insert
First let's create our test table:
USE tempdb
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1
GO
CREATE TABLE t1 (id INT PRIMARY KEY, name1 VARCHAR(10))
INSERT INTO t1
SELECT 1, 'name 1' UNION ALL
SELECT 2, 'name 2' UNION ALL
SELECT 3, 'name 3' UNION ALL
SELECT 4, 'name 4' UNION ALL
SELECT 5, 'name 5'
GO
Both methods when Inserting a row:
---------------------------
-- I N S E R T R O W ---
---------------------------
DECLARE @rc INT
-- NO EXISTS ------------
BEGIN TRAN
-- update the row with id = 6.
UPDATE t1
SET name1 = 'name 6'
WHERE id = 6
-- get rowcount
SELECT @rc = @@ROWCOUNT
-- see what locks are being held
SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - No Exists - after update'
FROM sys.dm_tran_locks
-- if update rowcount = 0 means that there is no such row so we insert it
IF @rc = 0
BEGIN
INSERT INTO t1
SELECT 6, 'name 6'
-- see what locks are being held
SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - No Exists - after insert'
FROM sys.dm_tran_locks
END
ROLLBACK
-- EXISTS ---------------
BEGIN TRAN
-- if row already exists in a table update it else insert new row
IF EXISTS (SELECT * FROM t1 WHERE id = 6)
BEGIN
-- see what locks are being held from select
SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after select'
FROM sys.dm_tran_locks
UPDATE t1
SET name1 = 'name 6'
WHERE id = 6
SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after update'
FROM sys.dm_tran_locks
END
ELSE
BEGIN
-- see what locks are being held from select
SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after select'
FROM sys.dm_tran_locks
INSERT INTO t1
SELECT 6, 'name 6'
SELECT resource_type, request_mode, resource_description, 'ROW NOT IN TABLE - Exists - after insert'
FROM sys.dm_tran_locks
END
ROLLBACK
GO
When a row doesn't exist in a table we have to insert it.
The NO EXISTS method is more expensive due to the IX page lock by update and insert followed by X Lock by insert.
The EXISTS method uses a select * to check if the row exists using only an IS (Intent Shared) lock which is inexpensive compared to the IX lock made by the Update. This IS lock is then followed by an IX and X Lock made by the Insert.
Both methods when Updating a row:
---------------------------
-- U P D A T E R O W ---
---------------------------
DECLARE @rc INT
-- NO EXISTS ------------
BEGIN TRAN
-- update the row with id = 5
UPDATE t1
SET name1 = 'name 6'
WHERE id = 5
-- get rowcount
SELECT @rc = @@ROWCOUNT
-- see what locks are being held
SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - No Exists - after update'
FROM sys.dm_tran_locks
-- update rowcount = 1 means that there is a row so we updated it
IF @rc = 0
BEGIN
INSERT INTO t1
SELECT 6, 'name 6'
-- see what locks are being held
SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - No Exists - after insert'
FROM sys.dm_tran_locks
END
ROLLBACK
-- EXISTS ---------------
BEGIN TRAN
-- if row already exists in a table update it else insert new row
IF EXISTS (SELECT * FROM t1 WHERE id = 5)
BEGIN
-- see what locks are being held from select
SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after select'
FROM sys.dm_tran_locks
UPDATE t1
SET name1 = 'name 6'
WHERE id = 5
SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after update'
FROM sys.dm_tran_locks
END
ELSE
BEGIN
-- see what locks are being held from select
SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after select'
FROM sys.dm_tran_locks
INSERT INTO t1
SELECT 6, 'name 6'
SELECT resource_type, request_mode, resource_description, 'ROW IN TABLE - Exists - after insert'
FROM sys.dm_tran_locks
END
ROLLBACK
GO
When a row exist in a table we have to update it.
The NO EXISTS method is less expensive due to only one IX and X Lock made by the Update.
The EXISTS method again uses a select * to check if the row exists using an IS (Intent Shared) lock followed by an IX and X Lock made by the Update.
Here the NO EXISTS is better because it doesn't need an IS (Intent Shared) lock.
However on overall lock use i much rather go with EXISTS method than NO EXISTS.
