While it is convenient to have SQL statements generate values, it can be problematic if the generated values need to be known and used. For example, it is a widespread and common practice to define a table using IDENTITY to populate primary key values during INSERT statements. Sometimes, there is a need to know the IDENTITY value immediately after an INSERT statement is completed  —  perhaps, to do INSERTs into child tables. Additionally, when tables have default values set by functions such as GETDATE(), there are times when these values need to be known after the INSERT. Similarly, an UPDATE statement can explicitly set a column’s new value to a value known only at execution time (e.g., setting the current date and time to GETDATE() or calculating the value of a computed column).

It is common to use the @@IDENTITY function, SCOPE_IDENTITY() function, or IDENT_CURRENT(‘tableName’) to return the most recently generated IDENTITY value. On a cautionary note, it is important to understand that SELECT @@IDENTITY has server-level scope and could return the most recent value from another statement, possibly even from another database. I have seen itwritten incorrectly that by placing both the INSERT statement and the SELECT @@IDENTITY statement within a transaction, SELECT @@IDENTITY will return the correct value. That simply isn’t true, under any circumstances. For example, the table could have an INSERT trigger that inserts into a table that uses IDENTITY. SQL Server 2000 introduced the SCOPE_IDENTITY() function to return the IDENTITY value, only for the current scope. The .NET Framework and ADO.NET offers application developers ways to find the IDENTITY value using application code. Refer to the Knowledge Base article 320301 and Knowledge Base article 320141 for more information.

Getting Autogenerated Values the Old-Fashioned Way

The AdventureWorks database in SQL Server 2005 has a table that is ideal for demonstrating how to get back multiple system-generated values from an INSERT. Take a look at the DDL to create the ProductDescription table in the Production schema:

CREATE TABLE [Production].[ProductDescription](
      [ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL,
      [Description] [nvarchar](400) NOT NULL,
      [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT 
[DF_ProductDescription_rowguid]  DEFAULT (newid()),
      [ModifiedDate] [datetime] NOT NULL CONSTRAINT
[DF_ProductDescription_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_ProductDescription_ProductDescriptionID] PRIMARY KEY CLUSTERED 
(
      [ProductDescriptionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

As you can see, three of the four columns in the table can have their values automatically populated. An INSERT statement specifying only the value of the Description column is valid:

insert Production.ProductDescription (Description)
values ('DBAzine demo')

Obtaining all of the system-generated values requires a multi-statement solution on SQL Server 2000:

insert Production.ProductDescription (Description)
values ('DBAzine demo')

select ProductDescriptionID, rowguid, ModifiedDate
from Production.ProductDescription
where ProductDescriptionID = SCOPE_IDENTITY()

Notice that this technique requires two trips to the database.

The OUTPUT Clause

SQL Server 2005 offers a much more convenient way of determining the system-generated values by attacking the problem at the source, within the INSERT statement itself. By adding an OUTPUT clause to an INSERT statement, it is easy to determine all values created during an INSERT; not just IDENTITY values, but also, values created by function calls such as GETDATE() or computed columns. Since you don’t know the value of a computed column until an INSERT or UPDATE is executed, an OUTPUT clause is perfect for obtaining the value of the computed column. An OUTPUT clause can also be used for auditing changes made by an UPDATE or DELETE.

An OUTPUT clause uses syntax familiar to people who understand DML triggers. DML triggers expose special tables, namely, the inserted and deleted tables. An OUTPUT clause exposes these same special tables. It is assumed that the reader understands how these special tables are used in triggers.

Understand that like @@IDENTITY, using an OUTPUT clause does not necessarily guarantee that the values you seek will be returned. A trigger on the table of interest could alter the expected outcome. The OUTPUT clause executes before the trigger is executed. If the trigger alters the values that are returned in the OUTPUT clause, the OUTPUT clause’s returned values will be out of date, having been superseded by what the trigger or triggers did. Whatever approach you use, be sure to thoroughly test your changes.

Using OUTPUT with INSERT

Getting the inserted values from an OUTPUT statement requires the use of the inserted special table. The values populated by IDENTITY and the DEFAULTs can be retrieved from the inserted table.

insert Production.ProductDescription (Description)
output Inserted.ProductDescriptionID, Inserted.rowguid, Inserted.ModifiedDate
values ('DBAzine OUTPUT clause demo')


ProductDescriptionID rowguid                              ModifiedDate
-------------------- ------------------------------------ -----------------------
2014                 7C4CAD36-DD19-43D7-B5E2-648C44578762 2005-11-05 17:24:31.813

You are not restricted to querying the inserted table for only the columns that were automatically populated. You may query the inserted table for any column.

A trigger on a table can change the results to something unexpected. After OUTPUT returns the values from the statement to which it applies, a trigger can alter the final values that are stored in the table. In this example, if there were an INSERT trigger on the ProductDescription table, the trigger could, for example, change the value of ModifiedDate to something different from the value supplied by the GETDATE() function.

Sometimes, people will ask if an INSERT with an OUTPUT clause is really shorthand for SQL Server 2005 to internally execute a SELECT after an INSERT. The definitive way to answer questions about what takes place internally is to run the SQL Profiler. The INSERT with the OUTPUT appears as a single statement in the Profiler, not as an INSERT followed by a SELECT. This is important from a performance perspective because a single statement means a single trip to the database.

Using OUTPUT with UPDATE

When using OUTPUT with an UPDATE statement, the inserted and deleted tables can be referenced. The deleted table contains the original values before the UPDATE, and the inserted table contains the new values after the UPDATE. Examine the following UPDATE statement:

update Production.ProductDescription
set Description = 'DBAzine OUTPUT INTO demo'
output deleted.Description
where Description = 'DBAzine OUTPUT clause demo'

Although the query is valid, it will not work because there is an UPDATE trigger on the Production.ProductDescription table. Attempts to execute the query will fail, resulting in this error message:

Msg 334, Level 16, State 1, Line 1
The target table 'Production.ProductDescription' of the DML statement cannot have any enabled triggers 
if the statement contains an OUTPUT clause without INTO clause.

To resolve this error, you must use the alternate form of the OUTPUT clause that utilizes the INTO keyword. The object of INTO is a table variable or a table, so one must be created.

declare @tv table (ProductDescriptionID int, Description nvarchar(400))

update Production.ProductDescription
set Description = 'DBAzine OUTPUT INTO demo'
output deleted.ProductDescriptionID, deleted.Description
into @tv
where Description = 'DBAzine OUTPUT clause demo'

(1 row(s) affected)

Because the results are retrieved into the table variable, the value obtained from the OUTPUT clause is not displayed. It is necessary to add a SELECT to query the table variable and display the result.

select Description from @tv

Description
------------------------
DBAzine OUTPUT INTO demo

(1 row(s) affected)

You can also reference the INSERTED table from within the OUTPUT clause.

Using OUTPUT with DELETE

When using the OUTPUT clause with a DELETE statement, the deleted table can be referenced as shown:

delete from Production.ProductDescription
output deleted.ProductDescriptionID
where Description = 'DBAzine OUTPUT clause demo'

ProductDescriptionID
--------------------
2014

(1 row(s) affected)

The INTO keyword is not required in this example because there isn’t a DELETE trigger on the Production.ProductDescription table, and a table variable is not being used. However, if you wanted to use a table variable, you could do so.

Conclusion

SQL Server 2005 has T-SQL enhancements to make common tasks easier. Although @@IDENTITY and SCOPE_IDENTITY() are still supported, you’ll probably find the OUTPUT clause a more convenient choice for knowing what values your queries are generating.

--

John Paul Cook is a database and systems architect in Houston, Texas. His primary focus is helping large enterprise customers succeed with regulatory compliance, SQL Server, C#, Oracle, and the .NET framework.