1. The created table structure id column is an auto-incrementing column.
Create table test
(
Id int identity (1, 1) not null,
Name
Nvarchar (20)
)
2.
SELECT SCOPE_IDENTITY () indicates the auto-increment ID.
In C #, you can directly insert int I = SELECT SCOPE_IDENTITY.
3. SCOPE_IDENTITY is explained as follows:
Returns the last IDENTITY inserted into the IDENTITY column in the same scope.
Value. A scope is a module-stored procedure, trigger, function, or batch processing. Therefore, if the two statements are in the same stored procedure, function, or batch processing, they are in the same scope.
Syntax
SCOPE_IDENTITY (
)
Return type
SQL _variant
Note
SCOPE_IDENTITY, IDENT_CURRENT, and
@ IDENTITY is similar in functionality because they all return values inserted into the IDENTITY column.
4. SCOPE_IDENTITY, IDENT_CURRENT, and @ IDENTITY are explained as follows:
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. For more information, see IDENT_CURRENT.
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
Not limited to specific scopes.
For example, there are two tables T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted into T1, the trigger is triggered and
Insert a row in 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 will be inserted on T1
Return different values at the end of the statement.
@ IDENTITY returns the value of the last IDENTITY column inserted to any scope in the current session. This value is used to insert T2
.
SCOPE_IDENTITY () returns the IDENTITY value inserted in T1, which is the last one that occurs in the same scope
INSERT. If the SCOPE_IDENTITY () function is called when an insert statement occurs in the scope before the identifiers column, the function returns NULL.