Merge faster than Insert

Question

Are there any advantages/disadvantages of making use of a merge over a insert function? Can an insert statement make use of the `OUTPUT'

Note: the ON clause for the merge is set to 1=0 (always insert)

Merge DeviceReading
    Using (
        --table here
    ) As [Source]
    On (1 = 0) -- Effectively Makes this an Insert as 1 Never Matches 0
    When Not Matched Then
        INSERT (
            [value1],
            [value2]
        ) VALUES (
            @value1,
            @value2
        ) OUTPUT Inserted.[value1], Inserted.[value2] Into @InsertIDTable;

Answer 1

MERGE has more flexible OUTPUT. OUTPUT can refer to the merge source which is handy if you want the client to be able to match what it sent to what was actually inserted (e.g. IDENTITY values). INSERT can't do that (for no fundamental reasons; seems to be not implemented).

I can't think of any performance difference. The plans certainly look so similar that I can't make out a difference right now. If there was a major difference SQL Server could optimize one form to the other.

Answer 2

After thinking about this, I decided to answer instead of comment to provide more detailed information.

Yes, an INSERT statement can use an OUTPUT clause. It can be specified before the VALUES clause. See the SQL Server Books Online (http://technet.microsoft.com/en-us/library/ms174335.aspx) for the authoritative T-SQL reference

I suggest you avoid MERGE for unconditional inserts in production code. It provides no additional functionality, requires less codes, and is easier to maintain. Comparing the execution plans of a MERGE versus INSERT, the `MERGE plan incudes additional scalar operators for the extra constants. I wouldn't expect a significant execution time difference but compilation time might be a slightly higher.

Below are functionally identical examples of these two techniques.

DECLARE @value1 varchar(10)
  , @value2 int;
DECLARE @test table ( col1 char(8000), col2 int );
MERGE test
USING
    ( SELECT    1 x
    ) AS [Source]
ON ( 1 = 0 ) -- Effectively Makes this an Insert as 1 Never Matches 0
WHEN NOT MATCHED THEN
    INSERT ( [col1], [col2] )
    VALUES ( @value1, @value2 )
OUTPUT
    Inserted.[col1]
  , Inserted.[col2]
    INTO @test;
GO
DECLARE @value1 varchar(10)
  , @value2 int;
DECLARE @test table ( col1 char(8000), col2 int );
INSERT  INTO dbo.test
        ( [col1], [col2] )
OUTPUT  Inserted.[col1], Inserted.[col2]
        INTO @test
VALUES  ( @value1, @value2 );
GO