How to accurately obtain the id value in SQL Server (1)

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')
 
 

Although all the above three functions can return the value of the inserted ID column generated by the database engine, they vary depending on the source of the inserted row (for example, stored procedure or trigger) and the connection to insert the row, these three functions have different functions.

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