IDENT_CURRENT returns the last identity value but SCOPE_IDENTITY does not

sql-server scope-identity
Pascal picture Pascal · Feb 21, 2012 · Viewed 31.9k times · Source

I have a SQL Server as backend for an asp.net application. Multiple people might insert data in the same table 'the same time...'.

When I read the solution/answer from this post: scope_identity vs ident_current

THEN I should not use the Ident_current because I could get the id of the insert of another user.

But using Select Scope_Identity(); returns me NULL while the Select IDENT_CURRENT('tableName') returns me the correct id which I checked with SQL Server Management Studio.

The insert statement I do within a SqlTransaction. The Select IDENT_CURRENT('tableName') is done after the transaction.

What do I wrong?

UPDATE:

My insert statement which is dynamically build together by a base class:

INSERT INTO TEST (NAME) VALUES (@Name)

The command's Parameter collection has the value "xxx" and everything is fine inserted into the table.

I do NOT use stored procedures just pure SqlDataReader with C#.

commandText = "INSERT INTO TEST (NAME) VALUES ('Test1');Select Scopy_Identity();"

How can I get the last auto inc id running the above statement and should I call ExecuteNonQuery or ExecuteReader for the above because it has a INSERT and SELECT that's confusing...

Answer

Fernando Manuel picture Fernando Manuel · Sep 30, 2012

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Related questions

SCOPE_IDENTITY() for GUIDs?

Can anyone tell me if there is an equivalent of SCOPE_IDENTITY() when using GUIDs as a primary key in SQL Server? I don't want to create the GUID first and save as a variable as we're using sequential GUIDs …

Read More
Why does select SCOPE_IDENTITY() return a decimal instead of an integer?

So I have a table with an identity column as the primary key, so it is an integer. So, why does SCOPE_IDENTITY() always return a decimal value instead of an int to my C# application? This is really annoying …

Read More
How to obtain SCOPE_IDENTITY() from INSERT run in EXEC() statement

I am building a dynamic insert statement within a stored procedure. I build up the sql syntax in a variable and then execute it with EXEC(@VarcharVariable). The SQL insert works fine but when I execute SET @Record_ID = Scope_…

Read More