What is @@IDENTITY ?

Very often, it is important to check the last identity value inserted into a table. This can be easily done using the system function “@@IDENTITY” . This System function returns the last inserted identity value. 

The function returns an integer value or a NULL. After an INSERT statement the @@IDENTITY function will contain the latest value inserted into the identity field. It will contain NULL, If the insert/ select statement does not affect any records. one Key feature that needs to be noted is that the @@IDENTITY value does not revert to a previous value even if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back. However the Triggers fired doesn’t have an impact on the function. Failed statements and transactions can change the  identity for a table and create gaps in the identity column values.

to check these , Execute the function with out performing an insert in a new session.

SELECT  @@IDENTITY
Result will be "NULL"

 Now insert some value into a table containing an identity field. If you do not have one, the below script might be of use. 


CREATE TABLE [dbo].[TestTable](
[Ident] [int] IDENTITY(100,1) NOT NULL,
[ColUmn_1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Insert the data by executing the statements below


INSERT INTO TESTTABLE ( COLUMN_1) VALUES ('HELLO');
SELECT  @@IDENTITY AS CURRENT_IDENTITY_VALUE
     , IDENT AS INSERTED_IDENTITY_VALUE
FROM
 TESTTABLE ;

Result:

Current_identity_value    Inserted_Identity_Value
100                       100

The Scope of the function is restricted to the current session. To get the identity value on a different server , we need to use functions / procedures.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: