Obtain the automatic ID of the last inserted record in SQL Server 2000

Obtain the automatic ID of the last inserted record in SQL Server 2000 in several ways. For more information, see the following description and comparison.

1. The simplest way to obtain this ID is: (here is a simple and practical example)

-- Create databases and tables
Create Database mydatabase
Use mydatabase
Create Table mytable
(
Id int identity (1, 1 ),
Name varchar (20)
)
-- Execute this SQL statement to check the values of the auto-incrementing column corresponding to the inserted record.
Insert into mytable values ('Li si ')
Select @ identity

2. Comparison of the three methods

SQL Server 2000 has three similar functions: scope_identity, ident_current, and @ identity. Both return values inserted into the identity column.
Ident_current returns the last generated id value for any session and specific tables in any scope. Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the value generated for a specific table in any session and scope.
@ Identity returns the last generated id value for any table in all scopes of the current session.
Scope_identity returns the last generated id value for the current session and any table in the current scope.
Scope_identity and @ identity return the last id value generated in any table in the current session. However, scope_identity only returns the value inserted into the current scope; @ identity is not limited to a specific scope.

For example, there are two tables T1 and T2, and an insert trigger is defined on T1. When a row is inserted to T1, the trigger is triggered and a row is inserted to T2. This example illustrates two scopes: insert on T1 and insert on T2 as the trigger result.

Assume that both T1 and T2 have the identity column. @ identity and scope_identity return different values at the end of the insert Statement on T1.

@ Identity returns the value of the last identity column inserted to any scope in the current session. This value is the value inserted in T2.

Scope_identity () returns the identity value inserted in T1, which is the last insert that occurs in the same scope. If the scope_identity () function is called when an insert statement occurs in the scope before the identifiers column, the function returns NULL.

The values returned by ident_current ('t1') and ident_current ('t2') are the last auto-increment values of the two tables.
Ajqc's experiment: (40 local threads, 40 + 40 remote threads are tested concurrently and W rows are inserted). The conclusion is:
1. in typical cascade applications. @ identity cannot be used. When there are more than 256 rows on cii850, m sd machines, concurrency conflicts will occur. on p42.8c and 512 m ddr, A concurrency conflict occurs when more than 6000 rows exist.
2. scope_identity () is absolutely reliable and can be used in the stored procedure without the need to create a trigger and avoid concurrent conflicts.
Select ident_current ('tablename') -- returns the last tag value generated in the specified table.
Select ident_incr ('tablename') -- returns the increment value of the marked field in the specified table.
Select ident_seed ('tablename') -- returns the seed value of the marked field in the specified table.

return the automatic number of the last inserted record
select ident_current ('tablename')
return the next automatic number:
select ident_current ('tablename ') + (select ident_incr ('tablename')
select @ identity -- returns the last tag value generated in all tables of the current session