MS SQL: Auto-increased key conflict

Question

Possible Duplicate:
Best way to get identity of inserted row?

Assume there is a large web application like a social network with many online users.

Users continually insert new row to a certain table. This application needs new row's ID after each insert, and this ID is an auto-increased int column.

Getting last raw's ID is done by SQL SELECT IDENT_CURRENT() as a separate function call via web application for each user.

Does it work properly and return correct ID if more than one user insert row at the same time?

No it doesn't work correctly. `IDENT_CURRENT` just returns the last id value generated for the table. From any session so not thread safe. It may not even be for a row that was successfully inserted. A rolledback insert will still increment `IDENT_CURRENT` — Martin Smith, Jan 15 '13 at 18:24

bummi · Answer 1 · 2013-01-15T19:59:50.313

Another save version ...

Insert Atab (na,nu)
OUTPUT INSERTED.id  
values('Text','Add')

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Reference

1

would you please explain it? – Maysam Jan 15 '13 at 19:23

HLGEM · Accepted Answer · 2013-01-16T15:00:46.290

Someone in our company used ident_current where he should have used scope_identity (and now should be using OUTPUT). It happened that the application was inserting a record at the same time as a data import of millions of records was running. The wrong ids got attached to the child records in the subsequent record import causing a data integrity problem that took a long time to figure out and fix. There is no circumstance where you should be using ident_current to grab a value you just inserted. It is a guarantee of data integrity problems.

If you are using a version of SQL server that has OUTPUT available, this is the technique that is preferred. Not only will OUTPUT give you the id just inserted but it can also give you other values inserted, so that you can get values for multiple record inserts more easily as a well as single ones You can also use it to find out what was deleted or updated. So it is an extremely valueable tool. Basically, you set up a table variable with the columns you want, then you use the OUTPUT clause in the INSERT, UPDATE or DELETE, then you can use the table variable afterwards to get the information you need for other processing steps. Some examples to see what you can do with output in an insert statement:

DECLARE @MyTableVar table( MyID int,
                           MyName varchar(50));

INSERT MyTable1 (MYName)
    OUTPUT INSERTED.MyID, INSERTED.MyName
        INTO @MyTableVar
VALUES ('test');

--Display the result set of the table variable.
SELECT MyID, MyName FROM @MyTableVar;
--Display the result set of the table.
Insert into table2 (MyID,test2, test2)
SELECT MyID, 'mttest1', 'mytest2'  FROM @MyTableVar;

Insert into table2 (MyID,field1, InsertedDate)
SELECT MyID, s.Field1, getdate()  FROM @MyTableVar t
join stagingtable s on t.MyName =  s.MyName

score 2 · Answer 3 · answered Jan 15 '13 at 18:28

You probably want to use SCOPE_IDENTITY() instead. Below is a breakdown of the differences between @@IDENTITY, IDENT_CURRENT() and SCOPE_IDENTITY (stolen from here):

SELECT @@IDENTITY It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value. @@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.

SELECT SCOPE_IDENTITY() It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value. SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.

SELECT IDENT_CURRENT(‘tablename’) It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

As you can see SCOPE_IDENTITY() will return the last ID generated by your current connection by a statement that is in scope. Based on what you are asking it sounds like this will fit your needs better.

Does it work in my situation? if 50 users insert a new row to a certain table will `SCOPE_IDENTITY()` will return the correct ID for each row for each user? — Maysam, Jan 15 '13 at 18:31
Yes, as long as you call `SCOPE_IDENTITY()` in the same scope. MS defines scope as: `A scope is a module: a stored procedure, trigger, function, or batch. ` — Abe Miessler, Jan 15 '13 at 18:35

MS SQL: Auto-increased key conflict

3 Answers3