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 incremental value of the tag field in the specified table select ident_seed ('tablename ') -- returns the seed value of the marked field in the specified table.
Returns the automatic number of the last inserted record.
========================================================== ======================================
Copy From http://lqingtao.blog.163.com/blog/static/3419280201211942815194/