MSSQL Gets the ID number of the current insert and how to handle it at high concurrency

In SQL Server 2000, insert data returns an AutoNumber ID, and there are three ways to implement Scope_identity, Ident_current, and @ @IDENTITY, all of which return values inserted into the IDENTITY column.

Ident_current: Returns the identity value that was last generated for any session and for a particular table in any scope. Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current returns the value generated for a specific table in any session and scope.

@ @IDENTITY: Returns the identity value that was last generated for any table in all scopes of the current session.

scope_identity: Returns the last identity value generated for the current session and any tables in the current scope

The scope_identity and @ @IDENTITY return the last identity value generated within any table in the current session. However, scope_identity only returns the value inserted into the current scope, and the @ @IDENTITY is not restricted to a specific scope.

Examples of Use:
set @NewID =scope_identity ()
Set @[email protected] @IDENTITY
Set @NewID =ident_current (' Table name ')
For example, there are two tables T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted into the T1, the trigger is fired and a row is inserted in the T2. This example illustrates two scopes: one is an insert on T1 and the other is an insert on T2 as the result of a trigger. Assuming that both T1 and T2 have an IDENTITY column, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1. @ @IDENTITY returns the last IDENTITY column value inserted into any scope in the current session, which is the value inserted into the T2. Scope_identity () returns the IDENTITY value in the Insert T1, which is the last insert that occurs in the same scope. The function returns a NULL value if the scope_identity () function is invoked before the INSERT statement to the IDENTITY column occurs in the scope. The values returned by Ident_current (' T1 ') and ident_current (' T2 ') are the last self-increment values of the two tables respectively.

AJQC Experiment: (40 local thread, 40+40 remote thread Concurrent test, insert 1200W Line), concluded that:

1. In a typical cascade application. Cannot use @ @IDENTITY, the cii850,256m SD machine on the 1W more rows will be concurrency conflict. On p42.8c,512m DDR, concurrency conflicts occur when there are more than 6,000 rows.

2.scope_identity () is absolutely reliable, can be used in the stored procedure, even the trigger is not built, no concurrency conflict
=================================

@ @IDENTITY and scope_identity ()
After an INSERT, SELECT into, or bulk copy statement is complete, the @ @IDENTITY contains the last identity value generated by the statement. If the statement does not affect any table that contains an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted and multiple identity values are generated, the @ @IDENTITY returns the last generated identity value. If the statement triggers one or more triggers, and the trigger performs an insert operation that generates an identity value, then the @ @IDENTITY is called immediately after the statement executes to return the last identity value generated by the trigger. If a trigger is triggered after an insert operation on a table that contains an identity column, and the trigger performs an insert operation on another table that does not have an identity column, the @ @IDENTITY returns the identity value that was first inserted. When an INSERT or SELECT into statement fails or a bulk copy fails, or if a transaction is rolled back, the @ @IDENTITY value does not revert 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.

@ @IDENTITY, scope_identity, and ident_current are similar functions because they all return the last value inserted into the IDENTITY column of the table.

@ @IDENTITY and scope_identity can return the last identity value 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.

Ident_current is not subject to scope and session restrictions, but is limited to the specified table. Ident_current can return identity values generated for a particular table in any session and in any scope.

The @ @IDENTITY function is scoped to the current session on the local server where the function is executed. This function cannot be applied to a remote or linked server. To obtain an identity value on another server, execute the stored procedure on the remote server or linked server and make the stored procedure (executed in the environment of a remote or linked server) collect the identity value and return it to the calling connection on the local server.

The following example inserts a row into a table that contains an identity column (LocationID) and uses the @ @IDENTITY to display the identity values used in the new row:

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


Add:
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.

==========================

@ @IDENTITY scope_identity () 2008-11-05 15:02


INSERT into Calculation_option (Option_type, vip_option_id, Option_code, Option_description, Last_updated_date, Last_ updated_by)
VALUES (@Option_Type, @VIP_Option_ID, @Option_Code, @Option_Description, GetDate (), @Last_Updated_By)

SELECT @Option_ID = scope_identity ()

Calculation_option the option_id in this table is automatically growing, scope_identity () is to get the auto-growth value

This place is scope_identity ().

The following references MSDN

For example, there are two tables T1 and T2, and INSERT triggers are defined on T1. When a row is inserted into the T1, the trigger is fired and a row is inserted in the T2. This scenario demonstrates two scopes: inserts on T1, and inserts in T2 through triggers.

Assuming that both T1 and T2 have identity columns, @ @IDENTITY and scope_identity will return different values at the end of the INSERT statement on T1. @ @IDENTITY Returns the value of the last identity column inserted within any scope in the current session. This is the value inserted in the T2. Scope_identity () returns the IDENTITY value that is inserted in the T1. This is the last insert that occurs within the same scope. If the scope_identity () function is called before any INSERT statement acts on the identity column in the scope, the function returns a null value.



=====================

CREATE TABLE #T (ID INT IDENTITY (PRIMARY) key,name VARCHAR (20))
INSERT into #T SELECT ' A '
SELECT @ @IDENTITY as ' @ @IDENTITY ',
Scope_identity () as ' scope_identity ',
Ident_current (' #T ') as ' ident_current '

MSSQL Gets the ID number of the current insert and how to handle it at high concurrency