Sql-server – SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined

sql serversql-server-2008

for performance reasons, we're considering changing our standard stored procedure for saving data (INSERT and UPDATE combined in a single stored proc):

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    IF EXISTS(SELECT 1 FROM tblCustomer WHERE CustomerID = @CustomerID)
        BEGIN
            UPDATE tblCustomer
            SET 
                CustomerName = @CustomerName
            WHERE
                CustomerID = @CustomerID;
                SELECT @New_ID = @CustomerID;
        END
    ELSE
        BEGIN
            INSERT INTO tblCustomer(
                Taalnaam)
            VALUES(
                @CustomerName)
                SELECT @New_ID = scope_identity();
        END
    END

into a syntax using the MERGE statement:

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    SELECT @New_ID = @idTaal;

    MERGE dbo.tblCustomer as target
        USING (SELECT @CustomerID, @CustomerName) as source (CustomerID, CustomerName)
        ON (target.CustomerID = source.CustomerID)
        WHEN MATCHED THEN
            UPDATE SET 
                CustomerName = @CustomerName
        WHEN NOT MATCHED THEN
            INSERT (CustomerName)
            VALUES(source.CustomerName);
                SELECT @New_ID = scope_identity();
    END

Question:

  • is this going to benefit us performance wise? (avoiding the initial SELECT on the primary key)

  • is this proper use of the MERGE statement? Most examples show MERGE statements in a scenario to execute multiple DML operations.

Best Answer

I haven't done any comparative testing of the two (yet) nor seen any articles on the topic. There is an Optimizing MERGE Statement Performance article on Technet but this doesn't include any comparisons with the update/insert syntax.

I can however suggest an improvement over your original syntax which eliminates the IF EXISTS lookup:

UPDATE 
    dbo.tblCustomer
SET 
    CustomerName = @CustomerName
WHERE
    CustomerID = @CustomerID;

IF (@@ROWCOUNT = 1)
BEGIN
    SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
    INSERT 
        dbo.tblCustomer
        (Taalnaam)
    VALUES
        (@CustomerName);

    SELECT @New_ID = SCOPE_IDENTITY();
END

You may also be interested in Mythbusting: Concurrent Update/Insert Solutions, which includes some examples of MERGE usage.

Related Question