Types of identity in sql server with examples

Question

If you have a product table and on that insert trigger is created. So, which identity function you will be used to return, which one first identity value generated on the table.

Answer

CREATE TABLE Product

(

       RowNo INT IDENTITY,

       ProductName VARCHAR(50),

       Price MONEY

)

CREATE TRIGGER TRG_Product

ON Product

FOR INSERT

AS

INSERT INTO Product(ProductName,Price) SELECT ProductName,Price FROM INSERTED

INSERT INTO Product(ProductName,Price)VALUES('A',10)

SELECT * FROM Product

RowNo   ProductName   Price

-----   -----------   -----

1       A             10.00

2       A             10.00

(2 row(s) affected)

You will see here, two record inserted on the table, first from your query and next from trigger fire on that.

Now run the each identity function.

SELECT @@IDENTITY

Results

2

Here, you will received the last identity value generated in the current scope.

SELECT SCOPE_IDENTITY()

Results

1

Here, you will received the first identity value generated by a trigger in current scope.

SELECT IDENT_CURRENT('Product')

Results

2

Here, you will received the last identity value generated in specified table in any scope.

So, correct answer is SCOPE_IDENTITY()

Note:-

@@IDENTITY

Return the last identity value generated in current scope

SCOPE_IDENTITY()

Return the first identity value generated by a trigger or a user defined function in current scope, otherwise it's also return last identity.

IDENT_CURRENT()

Return the last identity value generated in specified table in any scope.