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
[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:
PhoneNumber does not exist in the
[Contact].[PhoneNumber] table the merge inserts a new row into the
[Contact].[PhoneNumber] and returns the new
PhoneNumber does exist then the merge updates the
PhoneNumber to be the same value, and returns the
Id for the existing row.
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.
Id is returned even if it exists, as it's used as an FK in a further insert.