Best way to obtain new uniqueidentifier from an SQL insert statement while still determining success or failure of insertion?

Question

I'm starting to work with uniqueidentifiers, and I'm encountering an unexpected issue.

First of all, where I'd typically use SCOPE_IDENTITY(), this is no longer possible with a uniqueidentifier, even though in concept it still involves an auto-generated id value as a result of the default (newid() or newsequentialid()) constraint.

I decided to use the OUTPUT clause in the INSERT statement to output the UUID to a table variable. Now that I think about it, the OUTPUT clause renders SCOPE_IDENTITY obsolete, considering it's a much clearer and more powerful way to achieve the same thing and more (e.g. gain clear and direct access to multiple auto-generated columns for all inserted rows).

With the use of OUTPUT, however, I'm now wondering how that affects the test of @@rowcount that would typically follow an insert. Will the @@rowcount reflect the number of rows inserted in the main statement or the number of rows inserted into the table variable by the output clause?

You might think it wouldn't make a difference (i.e. the count should be the same either way), but it does make a difference, because the documentation says that the OUTPUT clause will return values and populate the table even if the insert statement fails.

An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

It does mention that @@rowcount in particular will always reflect the outermost statement only when using OUTPUT, but it mentions this is context of a nested query. Since the OUTPUT clause in my case is part of the outermost statement, it's unclear whether @@rowcount will report the number of rows inserted into the output table if the insert statement fails.

    declare @new_uuid TABLE (ID uniqueidentifier);
    insert into Users (ID, PersonID, Username, Password, Notes, Enabled)
    output INSERTED.UUID into @new_uuid
        values (@id, @personid, @username, @password, @notes, @enabled )
    if (@@rowcount <> 1) goto fail; --does this reflect rows inserted into Users or @new_uuid?  What if the insert fails, and rows are still output to @new_uuid?

Answer 1

I have tested this behavior experimentally via the following TSQL code:

create function NEWOBJECTID() returns int as begin return 1 / 0; end --function that would typically perform work to create a new object id, but intentionally throws an error instead
go

declare @uuidtable table (UUID uniqueidentifier);

insert into Users (ID)
output INSERTED.UUID into @uuidtable --UUID column has default constraint of (newid())
values (dbo.NEWOBJECTID()); --value to insert will throw an error

print @@rowcount; --called immediately after statement to see how it was affected by the failure
select * from @idtable; --see if anything was output into the table variable

The results of this statement were that the @@rowcount returned zero, and there are zero rows present in the @uuidtable variable, but please continue reading, because this result is misleading.

AT FIRST, this led me to believe that since no row was inserted, no OUTPUT occurs. This is false, and a simple modification proves it.

insert into Users (ID)
output INSERTED.UUID into @uuidtable --UUID column has default constraint of (newid())
values
(1), --value to insert should succeed
(2), --value to insert should succeed
(dbo.NEWOBJECTID()); --value to insert will throw an error

When I run it this time @@rowcount is still zero; however, 2 rows with two new uniqueidentifiers were OUTPUT into the @uuidtable.

This indicates that @@rowcount reflects the final number of rows inserted, which was zero, because although the first two values were inserted successfully and OUTPUT to the @uuidtable, the statement as a whole was rolled back as a result of the error.

Since two rows were inserted in the OUTPUT table, but zero rows were ultimately inserted as a result of the statement failing, and @@rowcount reports zero, that proves that it reflects the number of rows inserted by the insert statement itself rather than the number of rows inserted into the OUTPUT table along the way. This also confirms what the documentation says, that rows will be OUTPUT even if the overall statement fails.