Can I have problems with SQL @@identity and no triggers ?

Question

The code

ALTER PROCEDURE [dbo].[spSocial.QuestionsAddNew]
    @IdUser             int,
    @IdQuestionCategory     int,
    @Title                  int,
    @Body                   int,
    @CreatedDate            int,
    @ActivityDate           int,
    @VotesCount             int,
    @AnswersCount           int,
    @ViewedCount            int
AS
BEGIN
    SET NOCOUNT ON;

    insert into
        tblSocialQuestions
        (IdUser, IdQuestionCategory, Title, Body, CreatedDate, ActivityDate, VotesCount, AnswersCount, ViewedCount)
    values
        (@IdUser, @IdQuestionCategory, @Title, @Body, @CreatedDate, @ActivityDate, @VotesCount, @AnswersCount, @ViewedCount)

    select @@IDENTITY

    exec [spSocial.Questions2Users] @IdUser, 'AskedCount', 1

END

From what I understand

The @@identity function returns the last identity created in the same session.

The session is the database connection. The scope is the current query or the current stored procedure.

If the session is the current database connection, isn't there a problem in using @@Identity in a multithreaded environment as ASP .NET users requestes ?

If 10 users adds a new question at the same time, and [spSocial.QuestionsAddNew] stored procedure is executed in multithreaded environment with the same SqlConnection, isn't there a problem in using @@Identity ?

Answer 1

I think better to use scope identity instead of @@Identity

select scope_identity()

Answer 2

One ADO.NET connection can only execute one command at a time. In almost all cases, different ASP.NET threads will have different connections, which they keep around for the duration of a single page request. But even if the ASP.NET threads somehow shared a connection, they'd have to use the connection in such a way that only one thread can use a it at a time, or ADO.NET would raise an error.

So no, there's no danger in using @@IDENTITY in a multithreaded ASP.NET environment.

By the way, it's good practice not to use @@IDENTITY; use SCOPE_IDENTITY() instead. The latter also works after someone adds a trigger on the table.

Answer 3

There is a bug in sql server 2008 so you might get incorrect value. Details: SCOPE_IDENTITY() sometimes returns incorrect value

Answer 4

I'm not sure if 2005 has it, but if it does, the output clause is the best way to go. If not use scope_identity() as you can never predict when a trigger might be added and it could be some time before you find out that the wrong child relationships were set up and data that gets messed up that way is almost impossible to fix.