Usage of @ @IDENTITY in SQL Server

Original address: http://www.studyofnet.com/news/145.html

This article guide: @ @IDENTITY is the last inserted identity value, the identity value is generally referred to as the autogrow value. However, if you want to return only the values that are inserted into the current scope, use Scope_identity.

Usage:

Use SELECT @ @identity to get the ID that was automatically generated when the record was last inserted

If you use a stored procedure, it will be very simple, the code is as follows: SET @[email protected] @IDENTITY

Description

After an INSERT, SELECT into, or bulk copy statement is complete, the @ @IDENTITY contains the last identity value produced by this statement. If this statement does not affect any table with an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted, multiple identity values are generated, and the @ @IDENTITY returns the identity value that was last generated. If this statement fires one or more triggers that perform an insert operation that produces an identity value, the call @ @IDENTITY immediately after the statement executes returns the last identity value produced by the trigger. If the INSERT or SELECT into statement fails or the bulk copy fails, or the transaction is rolled back, the @ @IDENTITY value is not reverted to the previous setting.

If statements and transactions fail, they change the current identity of the table, causing the values in the identity column to become incoherent. Even if you do not commit a transaction that attempts to insert a value into the table, you can never roll back the identity value. For example, if an INSERT statement fails due to a ignore_dup_key conflict, the current identity value of the table will still increase.

The @ @IDENTITY, scope_identity, and ident_current functions are similar when you return to the last value of the @ @IDENTITY column that is inserted into the table.

@ @IDENTITY and Scope_identity will return the last identity value that was generated in all tables in the current session. However, scope_identity only returns values within the current scope, while the @ @IDENTITY is not limited to a specific scope.

@ @identity Instance

Use AdventureWorks;

GO

--display the value of LocationID in the last row in the table.

SELECT MAX (LocationID) from Production.location;

GO

INSERT into Production.location (Name, costrate, availability, ModifiedDate)

VALUES (' Damaged Goods ', 5, 2.5, GETDATE ());

GO

SELECT @ @IDENTITY as ' IDENTITY ';

GO

--display the value of LocationID of the newly inserted row.

SELECT MAX (LocationID) from Production.location;

GO

@ @identity Usage precautions:

We have to use @ @IDENTITY with caution because @ @IDENTITY it always gets the value of the increment field of the last change data.
The scope constraint where the change operation is made is ignored. For example, I have table A and table B two tables, and now I have an INSERT trigger defined on table A that automatically inserts a piece of data into table B when you insert a piece of data in table A. At this point, it is noted that there are two atomic operations: inserting a piece of data into a and then inserting a piece of data in B.

Now we want to assume that both table A and table B above have an IDENTITY increment domain, so when we insert a piece of data in table A, when we use the SELECT @ @IDENTITY output, is the value of the self-increment field of a or B in the output? The answer is clearly, who is the last person to insert the output, then B. So, I mean to want to get the self-increment domain value of a, the result is the self-increment domain value of B, a BUG with the birth, do not have to affect the whole system of data confusion.

Therefore, for this situation, we recommend that you use the @ @IDENTITY, and try to replace it with the scope_identity () function. Scope_identity () also gets the value of the last self-increment field, but it is limited to one operation, not @ @IDENTITY is the value of the self-increment field resulting from the last operation of the global operation.

Usage of @ @IDENTITY in SQL Server