Source: Internet Author: Unknown
- Create databases and tables
create database MyDataBase
use MyDataBase
create table mytable
id int identity（1，1），
name varchar（20）
）
--Execute this SQL to find out the value of the self-incremental column corresponding to the record that has just been inserted
insert into mytable values('Li Si')
select @@identity
2. Comparison of the three methods
In SQL Server 2000, there are three similar features: SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY, all of which return values inserted into the IDENTITY column.
IDENT_CURRENT Returns the last identification value generated for a specific table in any session and any scope. IDENT_CURRENT Not limited by scope and session, but by a specified table. IDENT_CURRENT Returns the value generated for a specific table in any session and scope.
@@IDENTITY Returns the last identification value generated for any table in all scopes of the current session.
SCOPE_IDENTITY Returns the last identified value that was generated for the current session and any table in the current scope
SCOPE_IDENTITY and @@IDENTITY return the last identification value that was generated within any table in the current session. However, SCOPE_IDENTITY only returns values inserted into the current scope; @@IDENTITY Not limited to a specific scope.
For example, there are two tables, T1 and T2, on which an INSERT trigger is defined. When a row is inserted into T1, the trigger is triggered and a row is inserted in T2. This example illustrates two scopes: one on T1 and one on T2 as a result of the trigger.
Assuming 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 last IDENTITY column value inserted into any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() returns the IDENTITY value inserted in T1, which is the last INSERT that occurred in the same scope. If the SCOPE_IDENTITY() function is called before the insertion statement to the identity column occurs in scope, the function returns a NULL value.
The values returned by IDENT_CURRENT('T1') and IDENT_CURRENT('T2') are the last self-incremental values of the two tables, respectively.
ajqc's experiment: (40 local threads, 40+40 remote threads simultaneously concurrent test, inserting 1200W rows), the conclusion is:
1. In typical cascading applications. @@IDENTITY cannot be used, and concurrent conflicts will occur when 1W is multi-rowed on CII850, 256M SD machines. On P42.8C and 512M DDR, there is a concurrent conflict when there are only more than 6000 lines.
2.SCOPE_IDENTITY() is absolutely reliable and can be used in the storage process, even triggers do not need to be built, and there are no concurrent conflicts
SELECT IDENT_CURRENT('TableName') - Returns the last label value generated in the specified table
SELECT IDENT_INCR('TableName') - returns the marked field increment value for the specified table
SELECT IDENT_SEED('TableName') - returns the marked field seed value of the specified table
Returns the automatic number of the last inserted record
SELECT IDENT_CURRENT（'TableName'）
Return to the next automatic number:
SELECT IDENT_CURRENT（'TableName'）＋（SELECT IDENT_INCR（'TableName'））
SELECT @@IDENTITY - Returns the last marked value that was generated in all tables in the current session
|
Posted on 6/15/2016 11:45:40 AM
|
|
|
