What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
- The
@@identity
function returns the last identity created in the same session. - The
scope_identity()
function returns the last identity created in the same session and the same scope. - The
ident_current(name)
returns the last identity created for a specific table or view in any session. - The
identity()
function is not used to get an identity, it's used to create an identity in aselect...into
query.
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where the scope_identity()
and the @@identity
functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity()
function will return the identity created by the query, while the @@identity
function will return the identity created by the trigger.
So, normally you would use the scope_identity()
function.
scope_identity vs ident_current
In that case you need to write the table name, what happens if you decide to change the table name? You then also must not forget to update your code to reflect that. I always use SCOPE_IDENTITY unless I need the ID from the insert that happens in a trigger then I will use @@IDENTITY
Also the bigger difference is that IDENT_CURRENT will give you the identity from another process that did the insert (in other words last generated identity value from any user)
so if you do an insert and then someone does an insert before you do a SELECT IDENT_CURRENT you will get that other person's identity value
See also 6 Different Ways To Get The Current Identity Value which has some code explaining what happens when you put triggers on the table
SQL Server SCOPE_IDENTITY() vs @@IDENTITY
The only way scope_identity()
could have the prior id value in this context is if the INSERT
statement does not create any rows. In that situation, @@IDENTITY
isn't gonna fix anything. In fact, @@IDENTITY
is less specific, and therefore could only hope to make things worse.
What you can do is use a different variable for the second insert. Or, you could set @TheId
back to NULL
before the second insert runs. In this way, you'll be able to tell if something went wrong. @@rowcount
is also useful for this.
I did see this in the comments:
"The second insert did not fail as the record was found in the database."
I put it to you perhaps the record was already in the database, before the code ran. Moreover, if there is a constraint on the table this could be the reason why the insert fails.
@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT [sql server 2005]
I'm not entirely sure what these "knows bugs" in SCOPE_IDENTITY() should be. The only thing I'm currently aware of is here: Six reasons you should be nervous about parallelism mentioned as the very first point.
What is the difference of Scope_Identity and @@Identity
Compare
@@IDENTITY
It returns the last identity value generated for any table in the current session, across all scopes.
Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY
returns that identity record which is created by trigger.
SCOPE_IDENTITY
It returns the last identity value generated for any table in the current session and the current scope.
Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.
IDENT_CURRENT
It returns the last identity value generated for a specific table in any session and any scope.
In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.
Difference among @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT
If you want to return the value you just inserted (to use to insert to child tables),then generally you need to use scope _identity(). It is specific to the scope of the statement your connection ran. It wil nto give you anyone else's identity value.
@@identiy, is also specific to your scope, but it also includes trigger valuies in the scope and thus if the table has a trigger that also inserts to an identity, that is the identity returned. as such, it means that @@identity should not be used to return teh value you inserted as it will start returnign the worng value as soon as anyone adds a trigger.
Then there is ident_current. This is the most dangerous of all because it retuns the last identitiy onteh table no matter which coneection put it in. So if you use that to get an identity value, you need to be awre that it is not necessarlity related to the record your connection put in and using this to get the identity to use to insert to child tables is a guarantee of data integrity problems.
Newer version of SQL server have an OUTPUT clause, this is far superior to using any of the three items above as you can return a set of identities and the values of other fields as well.
What is the difference between scope_identity() and current_identity()?
IDENT_CURRENT is similar to the SQL Server 2000 identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values.
However, the scope and session on which last is defined in each of these functions differ:
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.
Source
SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT return null
Your "dbs" connection probably has some connection pooling.
Write SQL INSERT and then SELECT identity in the same SQL statement.
Best solution is to create a stored procedure to insert data and then return current identity.
IDENT_CURRENT returns the last identity value but SCOPE_IDENTITY does not
just use the OUTPUT Clause (Transact-SQL) and you can insert the data and select back all (even multiple) identities in the same statement:
INSERT INTO TEST (NAME) OUTPUT INSERTED.YourIdentity VALUES (@Name)
working example:
DECLARE @YourTable table (YourIdentity int identity(1,1) primary key, YourCol1 varchar(5))
INSERT INTO @YourTable (YourCol1) OUTPUT INSERTED.YourIdentity VALUES ('ABC')
OUTPUT:
YourIdentity
------------
1
(1 row(s) affected)
SELECT SCOPE_IDENTITY() Always Returns 1
Best way to write it is ..
RETURN SCOPE_IDENTITY()
You can also use @@IDENTITY
and IDENT_CURRENT
for this
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.
Therefore RETURN SCOPE_IDENTITY() is the best one.
Related Topics
Using a Database Table as a Queue
Performance of Like '%Query%' VS Full Text Search Contains Query
Getting the Id of a Row I Updated in SQL Server
MySQL How to Insert into [Temp Table] from [Stored Procedure]
How to Make an Average of Dates in MySQL
SQL Insert Without Specifying Columns. What Happens
How to Execute a Ms SQL Server Stored Procedure in Java/Jsp, Returning Table Data
Designing SQL Database to Represent Oo Class Hierarchy
Query for Count of Distinct Values in a Rolling Date Range
Grant Privileges for a Particular Database in Postgresql
Zero SQL Deadlock by Design - Any Coding Patterns
Solution to "Cannot Perform a Dml Operation Inside a Query"
Oracle: Updating a Table Column Using Rownum in Conjunction with Order by Clause
Pseudo_Encrypt() Function in Plpgsql That Takes Bigint
Insert Null/Empty Value in SQL Datetime Column by Default
Calculate Difference Between 2 Dates in SQL, Excluding Weekend Days