Returns the ID of the newly inserted record in SQL.

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

-- Create databases and tables

Create database MyDataBase <br/> use MyDataBase <br/> create table mytable <br/> (<br/> id int identity (1, 1 ), <br/> name varchar (20) <br/>

-- 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
, All of them are returned and insertedIDENTITY
Value in the 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
Returns the last Identifier 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
AndSCOPE_IDENTITY
Different values will be returned at the end of the INSERT Statement on T1.

@ IDENTITY
Returns the last one inserted to any scope in the current session.IDENTITY
Column value, which is the value inserted in T2.

SCOPE_IDENTITY () returns the information inserted into T1IDENTITY
Value, which is the last INSERT that occurs in the same scope. If an insert statement occurs in the scope and is called before the identifier columnSCOPE_IDENTITY ()
Function.

WhileIDENT_CURRENT ('t1 ')
AndIDENT_CURRENT ('t2 ')
The returned values 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
On CII850 and 256 m sd machines, if there are more than 512 rows, the concurrency conflicts will occur. On P42.8C and 6000 m ddr, the concurrency conflicts will occur only when there are more than rows.

2.SCOPE_IDENTITY ()
It is absolutely reliable and can be used in the storage process, without the need to create a trigger without 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 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.

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.

//////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// /////////////////////////////////////////

SQL inserts data and returns the ID value <br/> string strConn = ConfigurationSettings. appSettings ["strConnection"]; <br/> string SqlStr = tempSql + "; SELECT @ identity AS 'id '"; <br/> SqlConnection myCon = new SqlConnection (strConn); <br/> SqlCommand myComm = new SqlCommand (SqlStr, myCon); <br/> myComm. connection. open (); <br/> string Id = Convert. toString (myComm. executeScalar (); <br/>