MS SQL: Auto-increased key conflict [duplicate]

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?

Answer 1

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

Answer 2

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