@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

by

It depends on what you are trying to do…

@@IDENTITY

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. @@IDENTITY is limited to the current session and is not limited to the current scope. For example, 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.

SCOPE_IDENTITY()

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() is similar to @@IDENTITY, but it will also limit the value to your current scope. 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.

IDENT_CURRENT()

Returns the last IDENTITY value produced in a table, regardless of the connection and scope of the statement that produced the value. IDENT_CURRENT is limited to a specified table, but not by connection or scope.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. How do I obtain a Query Execution Plan in SQL Server?
  4. Simple way to transpose columns and rows in SQL?
  5. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  6. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  7. SQL JOIN vs IN performance?
  8. Most efficient way in SQL Server to get date from date+time?
  9. Convert varchar into datetime in SQL Server
  10. TSQL Pivot without aggregate function
  11. How to generate a range of dates in SQL Server
  12. SQL RANK() versus ROW_NUMBER()
  13. Generate SQL Create Scripts for existing tables with Query
  14. How do I delete from multiple tables using INNER JOIN in SQL server
  15. Check if a row exists, otherwise insert
  16. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  17. How do I group on continuous ranges
  18. join comma delimited data column
  19. Count based on condition in SQL Server
  20. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  21. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  22. Alter user defined type in SQL Server
  23. Store multiple bit values in a single table column
  24. SQL Server equivalent to MySQL enum data type?
  25. How to correctly insert newline in nvarchar [duplicate]
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. SQL – Call Stored Procedure for each record
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment