What SCOPE_IDENTITY is
- SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
- SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
- A scope is a module; a Stored Procedure, trigger, function, or batch.
- Thus, two statements are in the same scope if they are in the same Stored Procedure, function, or batch.
- The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.
What IDENT_CURRENT is
- IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
- IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.
What @@IDENTITY is
- @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
- After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.
- If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
- If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
- The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
The differences between them are:
- SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
- SCOPE_IDENTITY and @@IDENTITY will return the last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope. A scope is a module; a Stored Procedure, trigger, function, or batch.
1. Create two tables as below:
CREATE TABLE Table1(id int IDENTITY)
CREATE TABLE Table2(id int IDENTITY(100,1))
2. Create a trigger on table1 as below:
CREATE TRIGGER TG_Table1 ON Table1 FOR INSERT
INSERT table2 DEFAULT VALUES
3. Do a select statement of both the tables:
SELECT * FROM Table1
SELECT * FROM Table2
4. Run the following SQL statements and observe the output
INSERT Table1 DEFAULT VALUES
SELECT @@IDENTITY — It will consider identity value changed by trigger as trigger is another scope.
SELECT SCOPE_IDENTITY() — It will NOT consider identity value changed by trigger as trigger is another scope.
5. Run the following SQL statements for ident_current:
6. Run the following SQL statements in a different query window, in other words a different session: