THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

Content now hosted at https://sql.kiwi/
More of my SQL Server technical articles are at https://sqlperformance.com/author/paulwhitenzgmail-com

A bug with Halloween Protection and the OUTPUT Clause

Background

The OUTPUT clause can be used to return results from an INSERT, UPDATE, DELETE, or MERGE statement. The data can be returned to the client, inserted to a table, or both.

There are two ways to add OUTPUT data to a table:

  1. Using OUTPUT INTO
  2. With an outer INSERT statement.

For example:

-- Test table
DECLARE @Target table
(
    id integer IDENTITY (1, 1) NOT NULL, 
    c1 integer NULL
);
-- Holds rows from the OUTPUT clause
DECLARE @Output table 
(
    id integer NOT NULL, 
    c1 integer NULL
);
--
-- Using OUTPUT INTO
--
-- Insert to the target table
INSERT @Target 
    (c1)
    -- Insert to the output table
    OUTPUT
        Inserted.id, 
        Inserted.c1 
    INTO @Output 
        (id, c1)
VALUES (1);
--
-- Using outer INSERT
--
-- Insert to the output table
INSERT @Output 
    (id, c1)
SELECT 
    SQ1.id, SQ1.c1
FROM 
(
    -- Insert to the target table
    INSERT @Target 
        (c1)
    OUTPUT
        -- Returns data to the outer INSERT
        Inserted.id, 
        Inserted.c1
    VALUES (1)
) AS SQ1;

The execution plan is the same for both forms:

OUTPUT clause execution plan

Notice there are two Insert operators. The new row is first added to the @Target table by the Clustered Index Insert, then added to the @Output table by the Table Insert operator.

Continue reading...
Anonymous comments are disabled
Privacy Statement