How to obtain the inserted id value in SQL Server

In actual database applications, we often need to get the inserted flag value to write data into the relevant table. But what we usually get is the value we need?
Sometimes we use select @ identity to obtain the value we just inserted, such as the following code

Code 1:
Use tempdb
If exists (select * From SYS. objects where object_id = object_id (n' [test1] ') and type in (n'u '))
Drop table [test1]
Go
Create Table test1
(
Id int identity (1, 1 ),
Content nvarchar (1, 100)
)
Insert into test1 (content) values ('solorez ')
Select @ identity

In optimistic situations, this is fine, but if we first run the following code 2 to create a trigger and then run code 3:

Code 2:
Create Table Test2
(
Id int identity (100,1 ),
Content nvarchar (1, 100)
)

Create trigger tri_test1_identitytest_ I
On test1 after insert
As
Begin
Insert into Test2
Select Content from inserted
End

Code 3:
Insert into test1 (content) values ('lorez2 ')
Select @ identity
 
We can see that the resulting id value is more than 100. Obviously, this is the ID value generated in Table Test2, and it is no longer what we want.
Let's take a look at the definition of @ identity: Identity
Originally, @ identity returned the id value inserted at the end of the current transaction.
In this case, we may use the following method:

Code 4:
Insert into test1 (content) values ('lorez3 ')
Select ident_current ('test1 ')

It seems that the result is still correct, but if we run code 4 multiple times and run the following code 5 at the same time:

Code 5:
Insert into test1 (content) values ('lorez3 ')

Waitfor delay '00: 00: 20'
Select ident_current ('test1 ')
 
The results are not what we want!
Let's take a look at the definition of ident_current (tablename): ident_current (tablename)
Returns the final id value of the specified table.

It is time to show the answer. We can use the following code:

Code 6:
Insert into test1 (content) values ('lorez3 ')
Select scope_identity ()

At this time, whether we add a trigger or run parallel insert, we always get the id value of the current transaction.

Scope_identity () Definition: scope_identity ()
 

 

There is a field in the table that is an auto-increment column (also known as the ID column, that is, identity increment). How can I get the id value of the next row of the column or the newly inserted id value?
 

After some exploration, the following code is valid:

-- Obtain the inserted id value

Select ident_current ('tablename') from tablename
 

-- Get the id value of the next row

Declare @ nextidentity int

Select @ nextidentity = ident_current ('tablename') + ident_incr ('tablename') from tablename

If (@ nextidentity is null)

Set @ nextidentity = ident_seed ('tablename') -- if the query table is empty, the start value of the ID column is used.

Replace tablename with the name of the table to be calculated.

 

Abstract: This article describes how to accurately obtain the id value in SQL Server. in SQL Server, there are three different functions that can help us achieve our goal.

SQL Server has three different functions that can be used to obtain the final id value in the table containing the ID column:

@ Identity scope_identity () ident_current ('data table name') All three functions can return the value of the inserted ID column generated by the database engine, but according to the source of the inserted row (for example: stored Procedures or triggers) and connections that Insert rows are different.

The @ identity function returns the id value (including any called stored procedures and triggers) generated when the current connection is inserted in all ranges ). This function is not only applicable to tables. The value returned by the function is the ID value generated by inserting rows in the last table.

The scope_identity () function is almost the same as a function. The difference is that the value returned by the former is limited to the current range (that is, the stored procedure in execution ).

The last is the ident_current function, which can be used for all ranges and connections to obtain the final table id value. Unlike the previous two functions, this function is only used for tables and uses [data table name] as a parameter.

We can take an example to demonstrate how the above functions work.

First, we create two simple example tables: one representing the customer table and the other representing the audit table. The purpose of creating an audit table is to track all records of information inserted and deleted in the database.

The following is a reference clip:

Create Table DBO. Customer
(Customerid int identity (1, 1) primary key)
Go
Create Table DBO. auditlog
(Auditlogid int identity (1, 1) primary key, customerid int, Action char (1), changedate datetime default getdate ())
Go
Then, we need to create a stored procedure and a secondary trigger. This stored procedure inserts a new customer row into the database table and returns the generated id value, the trigger inserts rows into the audit table:

The following is a reference clip:

Create procedure DBO. p_insertcustomer
@ Customerid int output
As
Set nocount on
Insert into DBO. Customer default values select @ customerid = @ identity
Go

Create trigger DBO. tr_customer_log on DBO. Customer for insert, delete
As
If exists (select 'x' from inserted) insert into DBO. auditlog (customerid, Action) Select customerid, 'I' from inserted else if exists (select 'x' from deleted) insert into DBO. auditlog (customerid, Action) Select customerid, 'd from deleted
Go
Now we can execute the program to create the first line of the customer table. The following is a reference segment:

Declare @ customerid int exec DBO. p_insertcustomer @ customerid output select @ customerid as customerid

After the execution, the value of the first customer we need is returned, and the entries inserted into the audit table are recorded. So far, no problem has been found in the data display.

Assume that a customer service representative needs to delete the new customer from the database due to the deviation in the previous communication. Now we will delete the newly inserted customer line:

The following is a reference clip:

Delete from DBO. customer where customerid = 1 now, the customer worksheet is an empty table, while the audit worksheet has two rows -- the first row is the first row of the record inserted, and the second row is the record deleted customer records.

Now we can add the second customer information to the database and check the obtained id value:

The following is a reference clip:

Declare @ customerid int exec DBO. p_insertcustomer @ customerid output select @ customerid as customerid

Wow! Let's see what's going on! If we look at the customer worksheet again, we will find that although Customer 2 is created, the ID value returned by our program is 3! What is the problem? Recall that, as mentioned earlier, the @ identity function will return any stored procedure called by the main program or touch the final id value generated by any trigger, it depends on which id value is generated before the function is called. In our example, the initial range is p_insertcustomer, and then the trigger is used to record the tr_customer_log of the inserted entry. Therefore, the returned id value is the ID value generated by the trigger in the audit work table, instead of the ID value generated in the customer work table.

In versions earlier than SQL Server 2000, The @ identity function is the only way to obtain the id value. The SQL Server Development Team introduced the scope_identity () and ident_current functions in SQL Server 2000 to solve this problem. Therefore, in the old SQL Server version, it is troublesome to solve this problem. For SQL server6.5, we recommend that you remove the ID column and create an auxiliary table that can contain the next value you want to use to create an ID column. However, this method is not a clear solution.

Now let's modify the stored procedure to use the scope_identity () function, and re-execute the program to add the third customer entry:

The following is a reference clip:

Alter procedure DBO. p_insertcustomer @ customerid int output
As
Set nocount on
Insert into DBO. Customer default values select @ customerid = scope_identity ()
Go
Declare @ customerid int exec DBO. p_insertcustomer @ customerid output
Select @ customerid as customerid
The returned id value is 3, but this time we get the correct id value because we added the third customer entry. If we check the audit worksheet, we will find that the fourth entry records the newly inserted customer records. Because the scope_identity () function only applies to the current range and only returns the value of the current execution program, this avoids the problem just now.

As mentioned above, functions @ identity and functions scope_identity () are not only used for tables, but can be used as arguments unlike the ident_current function. If you use the functions @ identity and scope_identity (), you must be careful when setting the code to obtain the correct id value from the required table. From the table perspective, dropping these two functions only uses the ident_current function and specifying the table is a safer method. In this way, you can avoid getting the error id value, right? I remember that the function ident_current is not only cross-range, but also cross-connection. That is to say, the value generated using this function is not only limited to the Program executed by your connection, but also covers all the connections to the entire database. Therefore, even in a small OLTP environment, it may not return the required value accurately. In this case, data corruption similar to the previous @ identity function/trigger may occur.

My suggestion is that the scope_identity () function is the safest function among the three functions and should be set as the default function. With this function, you can safely add triggers and sub-stored processes without worrying about unexpected data damage. The other two functions can be reserved for special situations. When you need to use these two functions, we recommend that you record their usage and test them.

TIPS:

SQL Server determines that the table has an ID column

If exists (select * From syscolumns where id = object_id (N 'test1') and columnproperty (ID, name, 'isidentity ') = 1)
Print n 'with auto-incrementing column'
Else
Print n' No auto-incrementing column'

SQL Server displays the table in which the current database contains the auto-incrementing Column
Select B. Name, A. * From syscolumns A, sysobjects B where a. ID = B. ID and columnproperty (A. ID, A. Name, 'isidentity ') = 1

SQL Server auto-increment field reset method:

Sqlserver Reset:

Truncate table ashare_cjhb
DBCC checkident (ashare_cjhb, reseed, 0)

Obtain the last ID of the last SQL impact record. Use @ identity with caution. the ID of the last record commonly used to obtain the SQL impact is @ identity, but in some cases there may be problems, in fact, SQL provides three different methods.

Select scope_identity ()

Select ident_current (tablename)

Select @ identity

Ident_current is similar to the scope_identity and @ identity functions of SQL Server 2000. All three functions return the last generated id value. However, the "last" scope defined in each of the above functions is different from that defined in sessions.

Ident_current returns the latest id value generated for a session and a specified table in the field.

@ Identity returns the latest id value generated for a table in the current session across all scopes.

Scope_identity returns the latest id value generated for the current session and a table in the current scope.

When the ident_current function is called in an empty table, this function returns NULL.

If the statement and transaction fail, they will change the current identifier of the table, causing inconsistency of values in the ID column. Even if you have not committed a transaction that tries to insert a value to the table, you will never be able to roll back the id value. For example, if the insert statement fails due to an ignore_dup_key conflict, the current table id value will still increase.

@ Identity indicates the new value of the seed after insertion. The example is as follows:
If a table job already exists, insert a data entry to the table,
Insert into jobs (job_desc, min_lvl, max_lvl)
Values ('accountant', 12,125)
Select @ identity as 'Identity '// obtain the table recognition value