Sql-server – Is the update in this MERGE Statement redundant

mergesql serversql-server-2008-r2t-sql

I'm trying improve the performance of a complicated stored procedure. Inside the stored procedure is the following MERGE statement. I'm not very familiar with the MERGE syntax.

The [Contact].[PhoneNumber] table looks like this:

CREATE TABLE [Contact].[PhoneNumber]
(
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [PhoneNumber] [nvarchar](255) NOT NULL
    [InsertedAtDateTimeUTC] [datetime2](7) NOT NULL DEFAULT(sysutcdatetime())
)

The merge looks like this:

;WITH _ContactPhoneNumbers ([PhoneNumber]) AS 
(
    SELECT DISTINCT ([#ChannelData].[CallerAni])
    FROM [#ChannelData]
)
MERGE [Contact].[PhoneNumber] WITH (HOLDLOCK) _target
USING [_ContactPhoneNumbers] _source ON [_target].[PhoneNumber] = [_source].[PhoneNumber]

WHEN NOT MATCHED THEN
    INSERT ([PhoneNumber])
    VALUES ([PhoneNumber])

WHEN MATCHED THEN
    UPDATE 
        SET [_target].[PhoneNumber] = [_source].[PhoneNumber]
        OUTPUT INSERTED.[Id], [_source].[PhoneNumber] INTO #Contact_PhoneNumber_Output ([Id], [PhoneNumber]);

I read this as:

If the PhoneNumber does not exist in the [Contact].[PhoneNumber] table the merge inserts a new row into the [Contact].[PhoneNumber] and returns the new Id. If PhoneNumber does exist then the merge updates the PhoneNumber to be the same value, and returns the Id for the existing row.

Is the UPDATE redundant?

If it's redundant I'll take it out and see if it increases the performance. There are several MERGE statements in the stored procedure which follows the same pattern (with the UPDATE) so it could be a useful gain.

The Id is returned even if it exists, as it's used as an FK in a further insert.

Best Answer

Something needs to be in the WHEN MATCHED THEN clause, otherwise you don't get the Id back when the row exists.

However, you don't need to update the target table.

DECLARE @dummy int;

;WITH    _ContactPhoneNumbers ( [PhoneNumber] )
              AS ( SELECT    DISTINCT
                            ( [#ChannelData].[CallerAni] )

                   FROM     [#ChannelData]
                 )
        MERGE [PhoneNumber]  WITH ( HOLDLOCK ) _target
        USING [_ContactPhoneNumbers] _source
        ON [_target].[PhoneNumber] = [_source].[PhoneNumber]
        WHEN NOT MATCHED THEN
            INSERT ( [PhoneNumber] )
            VALUES ([PhoneNumber] )
        WHEN MATCHED THEN
            UPDATE SET @dummy = 0
        OUTPUT
            INSERTED.[Id] ,                
            [_source].[PhoneNumber]
        INTO #Contact_PhoneNumber_Output ( [Id], [PhoneNumber] );

Given the information in Use Caution with SQL Server's MERGE Statement, I'm going to ditch the MERGE completely. There's loads of LCK_M_RS_U contention too so I think I'll be better off with simpler SQL which I understand better.

Related Question