SQL MERGE vs INSERT, UPDATE, DELETE Performance Considerations (2024)

I do not agree in the conclusion. The statement should be modified, because there are a lot on unnecessary code. If I run the following script with the original code and the modified code on my computer, MERGE is faster.

Merge 1

SQL Server Execution Times: CPU time = 502042 ms, elapsed time = 86864 ms.

Merge 2

SQL Server Execution Times: CPU time = 8018 ms, elapsed time = 7419 ms.

Upsert 1

SQL Server Execution Times: CPU time = 508219 ms, elapsed time = 82928 ms.
SQL Server Execution Times: CPU time = 2889 ms, elapsed time = 573 ms.

Upsert 2

SQL Server Execution Times: CPU time = 10861 ms, elapsed time = 8963 ms.
SQL Server Execution Times: CPU time = 2751 ms, elapsed time = 968 ms.

USE master;
GO
DROP DATABASE IF EXISTS MergeDemo;
GO
CREATE DATABASE MergeDemo;
GO
ALTER DATABASE MergeDemo SET RECOVERY SIMPLE;
GO
USE MergeDemo;
GO
DECLARE @UpperBound INT = 10010000;

WITH cteN (Number)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_columns AS s1
CROSS JOIN sys.all_columns AS s2
)
SELECT Number
INTO dbo.Numbers
FROM cteN
WHERE Number <= @UpperBound;
GO
CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers (Number)
WITH (FILLFACTOR = 100);

CREATE TABLE dbo.EmployeeDestination1
(
Id INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Merge_Employee_Id1 PRIMARY KEY,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL
CONSTRAINT DF_IsActive1 DEFAULT 1,
IsDeleted BIT NOT NULL
CONSTRAINT DF_IsDeleted1 DEFAULT 0
);

CREATE TABLE dbo.EmployeeDestination2
(
Id INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Merge_Employee_Id2 PRIMARY KEY,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL
CONSTRAINT DF_IsActive2 DEFAULT 1,
IsDeleted BIT NOT NULL
CONSTRAINT DF_IsDeleted2 DEFAULT 0
);

CREATE TABLE dbo.EmployeeDestination3
(
Id INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Merge_Employee_Id3 PRIMARY KEY,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL
CONSTRAINT DF_IsActive3 DEFAULT 1,
IsDeleted BIT NOT NULL
CONSTRAINT DF_IsDeleted3 DEFAULT 0
);

CREATE TABLE dbo.EmployeeDestination4
(
Id INT IDENTITY(1, 1) NOT NULL
CONSTRAINT PK_Merge_Employee_Id4 PRIMARY KEY,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL
CONSTRAINT DF_IsActive4 DEFAULT 1,
IsDeleted BIT NOT NULL
CONSTRAINT DF_IsDeleted4 DEFAULT 0
);

CREATE TABLE dbo.EmployeeSource
(
RowNumber INT NOT NULL,
EmployeeNumber CHAR(20) NOT NULL,
FirstName NVARCHAR(256) NOT NULL,
LastName NVARCHAR(256) NOT NULL,
MiddleIntial CHAR(1) NULL,
YearlySalary DECIMAL(36, 2) NOT NULL,
DepartmentCode CHAR(5) NOT NULL,
StartDate DATE NOT NULL,
IsActive BIT NOT NULL,
IsDeleted BIT NOT NULL
);
GO
INSERT INTO dbo.EmployeeDestination1
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
)
SELECT TOP (10000000)
CONCAT('E00', n.Number) AS EmployeeNumber,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 6) AS FirstName,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 7) AS LastName,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 1) AS MiddleInitial,
ABS(CHECKSUM(NEWID()) % 100000) + 45000 AS YearlySalary,
CONCAT('M000', ABS(CHECKSUM(NEWID()) % 3) + 1) AS DepartmentCode,
DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + DATEDIFF(DAY, '2020-01-01', '03-31-2023')), '2020-01-01') AS StartDate,
CASE
WHEN (n.Number % 1000) = 0 THEN
0
ELSE
1
END AS IsActive
FROM dbo.Numbers n;

INSERT INTO dbo.EmployeeDestination2
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
)
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
FROM dbo.EmployeeDestination1;

INSERT INTO dbo.EmployeeDestination3
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
)
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
FROM dbo.EmployeeDestination1;

INSERT INTO dbo.EmployeeDestination4
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
)
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive
FROM dbo.EmployeeDestination1;
GO
INSERT INTO dbo.EmployeeSource
(
RowNumber,
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
)
SELECT ROW_NUMBER() OVER (ORDER BY EmployeeNumber) AS RowNumber,
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination1
WHERE (Id % 10) = 0;

UPDATE dbo.EmployeeSource
SET YearlySalary = ABS(CHECKSUM(NEWID()) % 100000) + 45000,
IsDeleted = 1,
DepartmentCode = 'M0004'
WHERE RowNumber % 10= 0;

INSERT INTO dbo.EmployeeSource
(
RowNumber,
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsDeleted,
IsActive
)
SELECT TOP (100000)
ROW_NUMBER() OVER (ORDER BY n.Number) AS RowNumber,
CONCAT('E00', n.Number) AS EmployeeNumber,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 6) AS FirstName,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 7) AS LastName,
SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', (ABS(CHECKSUM(NEWID())) % 26) + 1, 1) AS MiddleInitial,
ABS(CHECKSUM(NEWID()) % 100000) + 45000 AS YearlySalary,
CONCAT('M000', ABS(CHECKSUM(NEWID()) % 3) + 1) AS DepartmentCode,
DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + DATEDIFF(DAY, '2020-01-01', '03-31-2023')), '2020-01-01') AS StartDate,
0 AS IsDeleted,
1 AS IsActive
FROM dbo.Numbers n
WHERE n.Number > 10000000;
GO
SELECT COUNT (*)
FROM dbo.EmployeeSource;

SELECT COUNT (*)
FROM dbo.EmployeeDestination1;

SELECT COUNT (*)
FROM dbo.EmployeeDestination2;

SELECT COUNT (*)
FROM dbo.EmployeeDestination3;

SELECT COUNT (*)
FROM dbo.EmployeeDestination4;
GO
PRINT 'Merge 1';

SET STATISTICS TIME ON;

MERGE dbo.EmployeeDestination1 AS dest
USING dbo.EmployeeSource AS src
ON (dest.EmployeeNumber = src.EmployeeNumber)
WHEN MATCHED AND EXISTS
(
SELECT FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeSource
EXCEPT
SELECT FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination1
) THEN
UPDATE SET FirstName = src.FirstName,
LastName = src.LastName,
MiddleIntial = src.MiddleIntial,
YearlySalary = src.YearlySalary,
DepartmentCode = src.DepartmentCode,
StartDate = src.StartDate,
IsActive = src.IsActive,
IsDeleted = src.IsDeleted
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
)
VALUES
(src.EmployeeNumber, src.FirstName, src.LastName, src.MiddleIntial, src.YearlySalary, src.DepartmentCode,
src.StartDate, src.IsActive, src.IsDeleted);

SET STATISTICS TIME OFF;
GO
PRINT 'Merge 2';

SET STATISTICS TIME ON;

MERGE dbo.EmployeeDestination2 AS dest
USING dbo.EmployeeSource AS src
ON (dest.EmployeeNumber = src.EmployeeNumber)
WHEN MATCHED THEN
UPDATE SET FirstName = src.FirstName,
LastName = src.LastName,
MiddleIntial = src.MiddleIntial,
YearlySalary = src.YearlySalary,
DepartmentCode = src.DepartmentCode,
StartDate = src.StartDate,
IsActive = src.IsActive,
IsDeleted = src.IsDeleted
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
)
VALUES
(src.EmployeeNumber, src.FirstName, src.LastName, src.MiddleIntial, src.YearlySalary, src.DepartmentCode,
src.StartDate, src.IsActive, src.IsDeleted);

SET STATISTICS TIME OFF;
GO
PRINT 'Upsert 1';

SET STATISTICS TIME ON;

UPDATE dest
SET dest.FirstName = src.FirstName,
dest.LastName = src.LastName,
dest.MiddleIntial = src.MiddleIntial,
dest.YearlySalary = src.YearlySalary,
dest.DepartmentCode = src.DepartmentCode,
dest.StartDate = src.StartDate,
IsActive = src.IsActive,
IsDeleted = src.IsDeleted
FROM dbo.EmployeeDestination3 AS dest INNER JOIN dbo.EmployeeSource AS src
ON dest.EmployeeNumber = src.EmployeeNumber -- JOIN Condition
WHERE EXISTS
(
SELECT FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeSource
EXCEPT
SELECT FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination3
);

INSERT INTO dbo.EmployeeDestination3
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
)
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeSource src
WHERE NOT EXISTS
(
SELECT src.EmployeeNumber
FROM dbo.EmployeeDestination3 AS dest
WHERE src.EmployeeNumber = dest.EmployeeNumber
);

SET STATISTICS TIME OFF;
GO
PRINT 'Upsert 2';

SET STATISTICS TIME ON;

INSERT INTO dbo.EmployeeDestination4
(
EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
)
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeSource AS src
WHERE NOT EXISTS
(
SELECT src.EmployeeNumber
FROM dbo.EmployeeDestination4 AS dest
WHERE src.EmployeeNumber = dest.EmployeeNumber
);

SET STATISTICS TIME OFF;
GO
SELECT COUNT (*)
FROM dbo.EmployeeDestination1;

SELECT COUNT (*)
FROM dbo.EmployeeDestination2;

SELECT COUNT (*)
FROM dbo.EmployeeDestination3;

SELECT COUNT (*)
FROM dbo.EmployeeDestination4;
GO
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination1
EXCEPT
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination2;

SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination3
EXCEPT
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination4;

SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination2
EXCEPT
SELECT EmployeeNumber,
FirstName,
LastName,
MiddleIntial,
YearlySalary,
DepartmentCode,
StartDate,
IsActive,
IsDeleted
FROM dbo.EmployeeDestination4;

SQL MERGE vs INSERT, UPDATE, DELETE Performance Considerations (2024)

FAQs

Is MERGE or UPDATE faster in SQL? ›

The UPDATE statement will most likely be more efficient than a MERGE if the all you are doing is updating rows. Given the complex nature of the MERGE command's match condition, it can result in more overhead to process the source and target rows.

Discover More ›

Is MERGE better than insert or UPDATE? ›

One of the main reasons I advocate MERGE is that it offers the ability to use a special OUTPUT clause to reference columns not part of the inserted or deleted tables. The standard OUTPUT clause works with INSERT, UPDATE, and DELETE; however, you can't reference columns not being inserted or deleted.

Discover More Details ›

Is MERGE better than DELETE and insert? ›

Overall less internal steps are performed in the merge compared to delete/insert. Furthermore the subselect in the delete command will be materialized in a temporary table and then replicated over all nodes, because the number or rows is below the replication border. This causes additional load in the network.

Keep Reading ›

Is SQL MERGE more efficient? ›

A MERGE statement can INSERT, UPDATE, and DELETE records in a single transaction, making it more readable and more efficient than having 3 separate statements.

Get More Info Here ›

What is the best way to speed up joins in SQL? ›

Follow the SQL best practices to ensure query optimization:

Index all the predicates in JOIN, WHERE, ORDER BY and GROUP BY clauses. ...
Avoid using functions in predicates. ...
Avoid using wildcard (%) at the beginning of a predicate. ...
Avoid unnecessary columns in SELECT clause. ...
Use inner join, instead of outer join if possible.

More items...

Learn More Now ›

What makes SQL run faster? ›

Now, we'll discuss the best SQL Server performance tuning practices and tips you may apply when writing SQL queries.

Tip 1: Add missing indexes. ...
Tip 2: Check for unused indexes. ...
Tip 3: Avoid using multiple OR in the FILTER predicate. ...
Tip 4: Use wildcards at the end of a phrase only. ...
Tip 5: Avoid too many JOINs.

More items...

Dec 23, 2021

Are INSERTs faster than updates? ›

You can't use UPDATE instead of INSERT, but the UPSERT statement is a good alternative. With the UPSERT statement, the database updates records where they are available and inserts them in the table if the record does not exist. INSERT is faster than an UPDATE statement provided a database is properly optimized.

Is delete faster than INSERT? ›

Inserting rows in a table is faster than deleting them. Loading data into a new table using create-table-as-select (CTAS) is faster still. So if you're removing most of the rows from a table, instead of issuing a delete you can: Create a new table saving the rows you want to keep.

Discover More ›

What is the benefit of MERGE statement in SQL? ›

Why is MERGE STATEMENT Used in SQL? MERGE in SQL helps in performing the repetitive tasks by a single query, which means that when we want to INSERT, DELETE and UPDATE data from a table. The MERGE statement comes in handy because It does three operations through a single MERGE statement.

Tell Me More ›

What is the difference between UPSERT and MERGE? ›

MERGE INTO is a SQL command using which you can achieve the UPSERT operation. thanks all. An 'Upsert' is an operation which performs either a row update or a row insert, depending on the pre-existence of the row. There is no 'UPSERT' command as such, rather the Upsert process is an extended form of the UPDATE command.

Discover More Details ›

Is bulk INSERT faster than INSERT SQL? ›

Bulk operations (f#,python,CSV) are faster than multiple single-row INSERT statements.

Find Out More ›

Does MERGE DELETE records? ›

The merge statement allows you to insert, update and delete rows in the target table in one go.

View Details ›

Which joins are faster SQL? ›

If the tables involved in the join operation are too small, say they have less than 10 records and the tables do not possess sufficient indexes to cover the query, in that case, the Left Join is generally faster than Inner Join. As you can see above, both the queries have returned the same result set.

Get More Info ›

What is the most efficient way to join multiple tables in SQL? ›

TLDR: The most efficient join is also the simplest join, 'Relational Algebra'. If you wish to find out more on all the methods of joins, read further. Relational algebra is the most common way of writing a query and also the most natural way to do so.

Is two queries faster than a join? ›

I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster. In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The reason is that joins mitigate the processing burden on the database by replacing multiple queries with one join query.

Know More ›

What is the difference between UPDATE and MERGE in SQL? ›

Key Differences between MERGE and UPDATE

With a MERGE, you can take different actions based on the rows matching or not matching the target or source. With the updated, youre only updating rows that match. Consider if you want to do synchronize all chance from one table to the next.

SQL MERGE vs INSERT, UPDATE, DELETE Performance Considerations (2024)

FAQs

Is MERGE or UPDATE faster in SQL? ›

Is bulk INSERT faster than INSERT SQL? ›

References