SA0118 : Use MERGE instead of INSERT...UPDATE or UPDATE...INSERT statements

Introduction

Using a MERGE statement instead of combining INSERT and UPDATE statement for merging tables can improve efficiency.

Description

Executing both INSERT and UPDATE statements successively on the same table may lead to performance issues. This pattern might indicate unnecessary data processing or can signal that a MERGE statement could be more efficient.

For example:

-- Example of two separate operations
INSERT INTO TableName (Column1, Column2) VALUES ('Value1', 'Value2');

UPDATE TableName SET Column2 = 'UpdatedValue' WHERE Column1 = 'Value1';

In this scenario, inserting data into a table only to immediately update it can cause unnecessary I/O operations. Consolidating these actions can reduce server load and improve efficiency.

• Increased I/O activity due to separate operations, which can slow down database performance.

• Potential for locking and blocking, affecting concurrency and scalability.

How to fix

Optimize data merging operations by replacing successive INSERT and UPDATE statements with a single MERGE statement to improve efficiency.

Follow these steps to address the issue:

1.Analyze your current INSERT and UPDATE operations on the table. If they modify the same data, consider if a MERGE statement is applicable.

2.Replace separate INSERT and UPDATE statements with a MERGE statement using the following structure:

3.Define the source of data and specify the target table in the MERGE statement.

4.Use the ON clause to specify the condition for the matched and unmatched records between the source and target.

5.Specify actions for WHEN MATCHED and WHEN NOT MATCHED scenarios, using the UPDATE and INSERT operations as needed.

For example:

MERGE INTO TableName AS target
USING (VALUES ('Value1', 'Value2')) AS source (Column1, Column2)
ON target.Column1 = source.Column1
WHEN MATCHED THEN
    UPDATE SET target.Column2 = 'UpdatedValue'
WHEN NOT MATCHED THEN
    INSERT (Column1, Column2) VALUES (source.Column1, source.Column2);

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

1 hour per issue.

Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL

-- Test 1: A pair of INSERT and UPDATE statements based on SELECT search predicate. Should generate SA0118.
INSERT INTO dbo.A_Table (Id,Data)
SELECT Id,Data FROM B_Table B
WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id)

UPDATE A_Table SET Data = B.Data
FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id

-- Test 1a: A pair of UPDATE and INSERT statements based on SELECT search predicate. Should generate SA0118.
UPDATE A_Table SET Data = B.Data
FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id

INSERT INTO dbo.A_Table (Id,Data)
SELECT Id,Data FROM B_Table B
WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id)

-- Test 1b: A pair of UPDATE and INSERT statements based on SELECT search predicate. Should generate SA0118.
UPDATE A SET Data = B.Data
FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id

INSERT INTO dbo.A_Table (Id,Data)
SELECT Id,Data FROM B_Table B
WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id)

-- Test 2: A pair of UPDATE and INSERT statements based on SELECT search predicate. Should ignore SA0118.
UPDATE A_Table SET Data = B.Data           -- IGNORE:SA0118
FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id

INSERT INTO A_Table (Id,Data)
SELECT Id,Data FROM B_Table B
WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id)

-- Test 3: A pair of UPDATE and INSERT statements having different target tables. Should NOT generate SA0118.
INSERT INTO dbo.A_Table2 (Id,Data)
SELECT Id,Data FROM B_Table B
WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id)

UPDATE A_Table1 SET Data = B.Data
FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id

-- Test 3: Not paired statements because the SELECT statement between them. Should NOT generate SA0118.
INSERT INTO A_Table (Id,Data)
SELECT Id,Data FROM B_Table B
WHERE NOT EXISTS (SELECT * FROM A_Table A WHERE A.Data = B.Data AND A.Id = B.Id)

SELECT * FROM A_Table

UPDATE A_Table SET Data = B.Data
FROM A_Table A INNER JOIN B_Table B ON A.Id = B.Id

Analysis Results

	Message	Line	Column
1	SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements.	2	0
2	SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements.	10	0
3	SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements.	18	0
4	SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements.	21	0

Related Topics

Analysis Rules