We will learn about the differences between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server in this essay.
What is SQL Server’s SCOPE_IDENTITY?
- The last IDENTITY number inserted into an IDENTITY column in the same scope is returned by SCOPE_IDENTITY.
- SCOPE_IDENTITY returns the most recently generated identity number for any table in the current session and scope.
- A module, a Stored Procedure, a trigger, a function, or a group are all examples of scopes. Thus, two lines in the same Stored Procedure, function, or batch are in the same scope.
- If the SCOPE_IDENTITY() function is called before any insert statements into an identity column appear in the scope, it will return NULL.
What exactly is IDENT_CURRENT in SQL Server?
- IDENT_CURRENT returns the most recently generated identity value for a particular table in any session and scope.
- IDENT_CURRENT is not scope or session restricted, but rather to a specific table.
What is SQL Server’s @@IDENTITY?
- @@IDENTITY returns the most recently generated identity number for any table in the current session across all scopes.
- Following the completion of an INSERT, SELECT INTO, or bulk copy statement, @@IDENTITY holds the last identity number generated by the statement.
- @@IDENTITY returns NULL if the statement did not affect any tables with identity fields. If multiple rows are inserted, producing multiple identity values, @@IDENTITY returns the most recently generated identity value.
- If the INSERT or SELECT INTO statement or bulk copy fails or the transaction is rolled back, the @@IDENTITY number does not revert to its prior value.
The distinctions between SCOPE IDENTITY, IDENTITY CURRENT, and IDENTITY
- SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are methods that return values that have been put into IDENTITY columns.
- SCOPE_IDENTITY and @@IDENTITY will yield the most recently produced identity values in any database in the current session. SCOPE_IDENTITY, on the other hand, gives numbers inserted only within the present scope; @@IDENTITY is not scope-specific. A module, a Stored Procedure, a trigger, a function, or a group are all examples of scopes.
Step 1. Create two tables as below.
CREATE TABLE Car(id int IDENTITY) CREATE TABLE Animal(id int IDENTITY(100,1))
Step 2. Create a Trigger on the table1 as below.
CREATE TRIGGER TG_Car ON Car FOR INSERT AS BEGIN INSERT animal DEFAULT VALUES END
Step 3. Make a select statement of both tables.
SELECT * FROM Car
SELECT * FROM Animal
Step 4. Run the SQL queries below and examine the results.
INSERT Car DEFAULT VALUES SELECT @@IDENTITY SELECT SCOPE_IDENTITY()
Step 5. Execute the SQL queries for ident_current listed below.
SELECT IDENT_CURRENT('Car') SELECT IDENT_CURRENT('Animal')
Step 6. Run the following SQL queries in a separate query window, or in another session.
SELECT @@IDENTITY SELECT SCOPE_IDENTITY() SELECT IDENT_CURRENT('Car') SELECT IDENT_CURRENT('Animal')