Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server 2008

What SCOPE_IDENTITY is

SCOPE_IDENTITY is:

  1. SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
  2. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
  3. A scope is a module; a Stored Procedure, trigger, function, or batch.
  4. Thus, two statements are in the same scope if they are in the same Stored Procedure, function, or batch.
  5. 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 is:

  1. IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  2. IDENT_CURRENT is not limited by scope and session; it is limited to a specified table.

What @@IDENTITY is

@@IDENTITY is:

  1. @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  2. After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement.
  3. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL.
  4. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated.
  5. 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.

Differences

The differences between them are:

  1. SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
  2. 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.

Example

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
AS
BEGIN
       INSERT table2 DEFAULT VALUES
END

3. Do a select statement of both the tables:

SELECT * FROM Table1

SQL1.jpg

SELECT * FROM Table2

SQL2.jpg

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.

SQL3.jpg

5. Run the following SQL statements for ident_current:

SELECT IDENT_CURRENT(‘Table1’)
SELECT IDENT_CURRENT(‘Table2’)

SQL4.jpg

6. Run the following SQL statements in a different query window, in other words a different session:

SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

SELECT IDENT_CURRENT(‘Table1’)
SELECT IDENT_CURRENT(‘Table2’)

SQL5.jpg

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s