SQL Bad Practices

It is a common business case to have to reuse the auto-generated SQL Server’s IDENTITY value. One way to deal with the problem is to use the system function @@IDENTITY. For example:

-- Create test table
IF NOT EXISTS(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Identity_Test')
CREATE TABLE Identity_Test(id int IDENTITY, value int)

-- Insert row and retrieve IDENTITY value
INSERT INTO Identity_Test(value) VALUES(NULL);
SELECT @@IDENTITY;

The problem with this code is that you may not retrieve the identity value that you inserted. For example, if there is a trigger on the table performing an insert on another table, you will get the last created identity value. Even if you never create any trigger, you may get skewed results with replicated tables since SQL Server creates his own replication triggers.

One way to deal with the problem is to use SCOPE_IDENTITY():

-- Create test table
IF NOT EXISTS(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Identity_Test')
CREATE TABLE Identity_Test(id int IDENTITY, value int)

-- Insert row and retrieve IDENTITY value
INSERT INTO Identity_Test(value) VALUES(NULL);
SELECT SCOPE_IDENTITY();

In theory, that should always provide the last value that you inserted. However, there is a nasty bug in SQL Server affecting SCOPE_IDENTITY() results when a query plan involving parallelism is generated. This is not the case in our example because INSERT INTO … VALUES won’t generate a parallel plan but it still is a serious issue when dealing with INSERT INTO … SELECT queries.

The recommended way of retrieving identity values (and the only one that can retrieve multiple values) is to use the OUTPUT clause:

-- Create test table
IF NOT EXISTS(select 1 from INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='Identity_Test')
CREATE TABLE Identity_Test(id int IDENTITY, value int)

-- Insert two rows and retrieve identity values
DECLARE @IDs TABLE (id INT)
INSERT INTO Identity_Test(value) OUTPUT inserted.id INTO @IDs VALUES(NULL);
INSERT INTO Identity_Test(value) OUTPUT inserted.id INTO @IDs VALUES(NULL);
SELECT id FROM @IDs;

See Microsoft Knowledge Base Article: http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779.