SQL obtains the last inserted ID values scope_identity, ident_current, and @ identity.

SQL Server 2000 has three similar functions: scope_identity, ident_current, and @ identity. Both return values inserted into the identity column.
Nbsp; ident_current returns the final id value generated for any session and a specific table 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 values for the current session and any table in the current scope. scope_identity and @ identity return the last generated id value for 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.
========================================================== ========================================================== ====
You may be familiar with @ identity in SQL Server. It is known that it is used to obtain the identity value of the last inserted data in the data table.
For example, if Table A has a field with ID auto-increment of 1 and the id value is 100, if I insert a piece of data to table A and
Select @ identity, then it returns the value of 101, the last identity domain (that is, the ID domain.

 

Now the question is, why should we use @ identity with caution? The reason is that @ identity always obtains the value of the auto-increment field of the last change data,
The scope constraints of the change operation are ignored. For example, I have two tables, table A and Table B. Now I have defined an insert trigger on Table,
When a piece of data is inserted in Table A, a piece of data is automatically inserted in table B. Note that there are two atomic operations: Insert a piece of data in a, and then insert a piece of data in B.

Now, assume that both table A and table B have an identity auto-incrementing domain. After inserting a data entry in Table A, we use
In the select @ identity output, is the output A or B's auto-incrementing Domain value? The answer is obvious, who will output the last insert,
That's B. So I wanted to get the auto-incrementing Domain value of A, and the result obtained the auto-incrementing Domain value of B. A Bug was born along with it.
Will affect the entire system data chaos.

Therefore, we recommend that you use @ identity with caution, and use the scope_identity () function whenever possible. Scope_identity ()
The value of the last auto-increment domain is obtained, but it is only within the range of an operation. Unlike @ identity, it is the last operation to take the global operation.
The value of the auto-increment field generated.
You may be familiar with @ identity in SQL Server. It is known that it is used to obtain the identity value of the last inserted data in the data table.
For example, if Table A has a field with ID auto-increment of 1 and the id value is 100, if I insert a piece of data to table A and
Select @ identity, then it returns the value of 101, the last identity domain (that is, the ID domain.

Now the question is, why should we use @ identity with caution? The reason is that @ identity always obtains the value of the auto-increment field of the last change data,
The scope constraints of the change operation are ignored. For example, I have two tables, table A and Table B. Now I have defined an insert trigger on Table,
When a piece of data is inserted in Table A, a piece of data is automatically inserted in table B. Note that there are two atomic operations: Insert a piece of data in a, and then insert a piece of data in B.

Now, assume that both table A and table B have an identity auto-incrementing domain. After inserting a data entry in Table A, we use
In the select @ identity output, is the output A or B's auto-incrementing Domain value? The answer is obvious, who will output the last insert,
That's B. So I wanted to get the auto-incrementing Domain value of A, and the result obtained the auto-incrementing Domain value of B. A Bug was born along with it.
Will affect the entire system data chaos.

Therefore, we recommend that you use @ identity with caution, and use the scope_identity () function whenever possible. Scope_identity ()
The value of the last auto-increment domain is obtained, but it is only within the range of an operation. Unlike @ identity, it is the last operation to take the global operation.
The value of the auto-increment field generated.
You may be familiar with @ identity in SQL Server. It is known that it is used to obtain the identity value of the last inserted data in the data table.
For example, if Table A has a field with ID auto-increment of 1 and the id value is 100, if I insert a piece of data to table A and
Select @ identity, then it returns the value of 101, the last identity domain (that is, the ID domain.

Now the question is, why should we use @ identity with caution? The reason is that @ identity always obtains the value of the auto-increment field of the last change data,
The scope constraints of the change operation are ignored. For example, I have two tables, table A and Table B. Now I have defined an insert trigger on Table,
When a piece of data is inserted in Table A, a piece of data is automatically inserted in table B. Note that there are two atomic operations: Insert a piece of data in a, and then insert a piece of data in B.

Now, assume that both table A and table B have an identity auto-incrementing domain. After inserting a data entry in Table A, we use
In the select @ identity output, is the output A or B's auto-incrementing Domain value? The answer is obvious, who will output the last insert,
That's B. So I wanted to get the auto-incrementing Domain value of A, and the result obtained the auto-incrementing Domain value of B. A Bug was born along with it.
Will affect the entire system data chaos.

Therefore, we recommend that you use @ identity with caution, and use the scope_identity () function whenever possible. Scope_identity ()
The value of the last auto-increment domain is obtained, but it is only within the range of an operation. Unlike @ identity, it is the last operation to take the global operation.
The value of the auto-increment field generated.
========================================================== ============================================

Prediction Method

The prediction method is actually relatively simple. We can set a primary key, but this primary key is not set to auto-increment, because before the insertion, we can use the program method to obtain a unique value as our primary key. this avoids the disadvantage that we cannot obtain the primary key after insertion, and because we predict the value we want to insert, after insertion, we can not use the method provided by the database, obtain the primary key again.

Here I recommend using a better prediction sequence, which is guid. as we all know, no two computers can produce the same guid value, and the guid generated on one machine will not be repeated.

Before inserting a database, we can use the guid function to obtain an available guid and use it as the primary key to insert it into the database.

However, this also has a disadvantage: GUID is generally 16 characters long and does not have any meaning, which may affect the performance of a large number of inserts.

Houzhifa

The post-knowledge method is a more practical method I introduced this time. The latest primary key value is obtained through two functions of SQL Server and one system variable.

The two functions are:

Ident_current

Scope_identity

The system variable value is:

@ Identity

Among them, the main difference between ident_current and scope_identity is that scope_identity is mainly used in a session and only the last identity of the table inserted in the current session, while ident_current has no scope concept, it is used for a specific table.

Here, I will mainly introduce the system variable @ identity. The following information is provided by SQL Server online help.

@ Identity
New information-September 10, September 2001

Returns the last inserted id value.

Syntax
@ Identity

Return type
Numeric

Note
After an insert, select into, or large-capacity copy statement is completed, @ identity contains the final id value generated by this statement. If this statement does not affect any table with an ID column, @ identity returns NULL. If multiple rows are inserted, multiple ID values are generated. @ identity returns the final id value. If one or more triggers are triggered to execute the insert operation that generates the id value, @ identity is called immediately after the statement is executed, and the last id value generated by the trigger is returned. If the trigger is triggered after the insert operation is executed on a table with an ID column and the trigger is inserted to another table without an ID column, @ identity returns the first inserted id value. If the insert or select into statement fails, the large-capacity copy fails, or the transaction is rolled back, the @ identity value is not restored to the previous setting.

The functions of @ identity, scope_identity, and ident_current are similar in returning the last value of the @ identity column inserted to the table.

@ Identity and scope_identity return the last id value generated in all tables of the current session. However, scope_identity only returns values within the current scope, and @ identity is not limited to specific scopes.

Ident_current is not restricted by the scope and session, but by the specified table. Ident_current returns the id value generated for a specific table in any session and in any scope. For more information, see ident_current.

@ The scope of the identity function is the local server that executes the function. This function cannot be applied to remote or linked servers. To obtain the id value of another server, run the stored procedure on a remote server or linked server and run the stored procedure (in a remote or linked server environment) collect the id value and return it to the call connection on the local server.

Example
The following example inserts a row into a table with an ID column and uses @ identity to display the id value used in the new row.

Insert into jobs (job_desc, min_lvl, max_lvl)
Values ('accountant', 12,125)
Select @ identity as 'identified'

The specific implementation method is to write a stored procedure and return @ identity after the insertion is complete. The following is an example of a stored procedure:

The following example creates two tables TZ and Ty, and creates an insert trigger on tz. When a row is inserted into the table tz, the trigger (ztrig) is triggered and a row is inserted in ty.

Use tempdb
Go
Create Table TZ (
Z_id int identity (1, 1) primary key,
Z_name varchar (20) not null)

Insert TZ
Values ('lisa ')
Insert TZ
Values ('Mike ')
Insert TZ
Values ('carla ')

Select * From TZ

-- Result set: this is how table TZ looks
Z_id z_name
-------------
1 Lisa
2 Mike
3 Carla

Create Table ty (
Y_id int identity (100,5) primary key,
Y_name varchar (20) null)

Insert ty (y_name)
Values ('boathouse ')
Insert ty (y_name)
Values ('rocks ')
Insert ty (y_name)
Values ('evator ')

Select * from Ty
-- Result set: this is how Ty looks:
Y_id y_name
---------------
100 boathouse
105 rocks
110 elevator

/* Create the trigger that inserts a row in Table ty
When a row is inserted in Table TZ */
Create trigger ztrig
On TZ
For insert
Begin
Insert ty values ('')
End

/* Fire the trigger and find out what identity values you get
With the @ identity and scope_identity functions */
Insert TZ values ('rosalie ')

Select scope_identity () as [scope_identity]
Go
Select @ identity as [@ identity]
Go

-- Here is the result set.
Scope_identity
4
/* Scope_identity returned the last identity value in the same scope, which was the insert on table TZ */

@ Identity
115
/* @ Identity returned the last identity value inserted to Ty by the trigger, which fired due to an earlier

It is worth noting that the affected table TZ returned by scope_identity in this process, while the Ty table affected by the trigger is not reflected, while @ identity reflects all currently affected tables, contains the Ty table affected by the trigger.

During use, we can use return @ identity to return the value obtained by a stored procedure, and then obtain the value in the program, so as to obtain the primary key generated after the current insertion.