There are 5 methods to get the last ID you entered, they are:
- @@ IDENTITY
- SCOPE_IDENTITY ()
- IDENT_CURRENT ('table name here')
- SELECT MAX
Below each one explored briefly.
Returns the last ID generated in the current user session. This ID may have been generated by an explicit INSERT command given by the user or indirectly by a trigger that has been executed within the same session. You have to be careful when using it.
Returns the last ID generated within the current scope. The current scope can be a stored procedure, trigger, or an explicit INSERT that has been given by the user. This is a more guaranteed way of knowing the last ID generated than @@ IDENTITY, since it avoids IDs that have eventually been generated by indirectly executed triggers.
This function returns the last ID generated for the table passed as parameter. Be careful, as some people have already written saying that this method is not transaction-safe .
For the last time, NO, you can not trust IDENT_CURRENT ()
The OUTPUT method is relatively recent (I believe from SQL Server 2005). To get the last record with it just use OUTPUT INSERTED.ID . As exemplified here link
It is very powerful because it allows you to know the last inserted ID, it allows the information of the included record to be inserted in another table, all with a clear and clear syntax. See an example taken from link
INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
VALUES (1, '[email protected]'), (2, '[email protected]');
Note that a record is being inserted into the User table and in the OUTPUT itself another one is inserted into the User_Log table.
This method consists of performing a SELECT MAX (CAMPO_ID) FROM TABLE. It is not indicated when you are inserting a record, because you spend an extra SQL to know information that can be obtained in the ways shown above. Also, if the value of the ID field is not an Integer (a GUID, for example), then this method will fail.