Use SQL Server to get the ID (AutoNumber) after inserting a record _mssql

A recent problem in the development project was to get the ID in the database immediately after inserting a record, and that ID was self increasing. There are several ways in which SQL Server 2005 can be implemented.
The easiest way to get this ID is to select @ @indentity after the query
--sql statements Create databases and tables

Copy Code code as follows:

Create DATABASE Dbdemo
Go
Use Dbdemo
Go
CREATE TABLE Tbldemo
(
ID int primary key identity (1,1),
Name varchar (20)
)
Go

--Execute the following SQL statement to find out the value of the self-added column that just inserted the record
INSERT into Tbldemo values (' Test ') SELECT @ @identity
In SQL Server 2000, there are three more similar features: Scope_identity, ident_current, and @ @IDENTITY, which return the values inserted into the IDENTITY column.

1) ident_current returns an identity value that is finally generated for any session and for a specific table in any scope, and is not limited by scope and session and is limited by the specified table.
2 @ @IDENTITY Returns the identity value that was last generated for any table in all scopes of the current session.
3) scope_identity returns the identity value that was last generated for any table in the current session and in the current scope。

The scope_identity and @ @IDENTITY return the last identity value generated in any table in the current session. However, scope_identity only returns the values 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 the T1. When a row is inserted into the T1, the trigger is fired and a row is inserted into the T2. This example illustrates two scopes: one is inserted on the T1 and the other is inserted as a result of the trigger on the T2.

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 last IDENTITY column value inserted into any scope in the current session, which is the value inserted into the T2.

SQL Server returns the AutoNumber ID of the last inserted record
one of the most recent problems in developing a project is getting its own ID in the database immediately after inserting a record in order to process the associated data. There are several ways in which you can do this in SQL Server 2000. Please see the following explanations and comparisons in detail.

The easiest way to get this ID is to: (Here's a simple and practical example)

--Creating 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 added column that just inserted the record
INSERT INTO mytable values (' Dick ')
SELECT @ @identity

Two or three different ways of comparison

In SQL Server 2000, there are three more similar features: Scope_identity, ident_current, and @ @IDENTITY, which return the values inserted into the IDENTITY column.
Ident_current returns the identity value that was last generated for any session and for a specific table in any scope. Ident_current are not limited by scope and session, but are limited by the specified table. Ident_current returns the values 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 identity value that was last generated for any table in the current session and in the current scope
The scope_identity and @ @IDENTITY return the last identity value generated in any table in the current session. However, scope_identity only returns the values 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 the T1. When a row is inserted into the T1, the trigger is fired and a row is inserted into the T2. This example illustrates two scopes: one is inserted on the T1 and the other is inserted as a result of the trigger on the T2.

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 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 occurs in the scope and before the identity column.

The values returned by Ident_current (' T1 ') and ident_current (' T2 ') are the last values of the two tables.

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

1. In a typical cascade application. No @ @IDENTITY, concurrency conflicts occur on a cii850,256m SD machine with 1W of rows. On the p42.8c,512m DDR, concurrency conflicts are only 6,000 rows.
2.scope_identity () is absolutely reliable, can be used in stored procedures, even triggers do not have to build, no concurrency conflict
SELECT ident_current (' tablename ')--Returns the last marked value generated in the specified table
SELECT ident_incr (' tablename ')--Returns the value of the Marked field increment for the specified table
SELECT ident_seed (' tablename ')--Returns the indicated field seed value for 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 generated in all tables in the current session

This is the case for SQL Server 2000, but how do you implement it, such as my SQL or Oracle? How to deal with it? I am also groping .... If a friend knows this way of handling, don't forget to tell, share together!