Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT

Jul 13, 2015
Introduction:

Here I will explain difference between @@identity, scope_identity and ident_current in sql server with example. Generally @@identity, scope_identity and ident_current properties in sql server is used to get identity / id value of last or newly inserted record in table but only difference is scope either local or global and session either current session or other session in sql server.

Description:


@@IDENTITY

It will return last or newly inserted record id of any table in current session but it’s not limited to current scope. In current session if any trigger or functions inserted record in any table that it will return that latest inserted record id regardless of table. We need to use this property whenever we don’t have any other functions or triggers that run automatically.

Syntax


SELECT @@IDENTITY

SCOPE_IDENTITY()

This property will return last or newly inserted record id of table in current session or connection and it’s limited to current scope that means it will return id of newly inserted record in current session / connection stored procedure or query executed by you in current scope even we have any other functions or triggers that run automatically. Its better we can go with property whenever we need to get last or newly inserted record id in table.

Syntax


SELECT SCOPE_IDENTITY()

IDENT_CURRENT

This property will return last or newly inserted record id of specified table. It’s not limited to any session or scope it’s limited to mentioned table so it will return last inserted record id of specified table.

Syntax


SELECT IDENT_CURRENT(table_name)

Finally we can say SCOPE_IDENTITY properties is best to get newly inserted record id from executed stored procedure or query when compared with other properties

Example


CREATE TABLE SAMPLE1 (Id INT IDENTITY)
CREATE TABLE SAMPLE2 (Id INT IDENTITY(100,1))
-- Trigger to execute while inserting data into SAMPLE1 table
GO
CREATE TRIGGER TRGINSERT ON SAMPLE1 FOR INSERT
AS
BEGIN
INSERT SAMPLE2 DEFAULT VALUES
END
GO

SELECT * FROM SAMPLE1  -- It will return empty value
SELECT * FROM SAMPLE2  -- It will return empty value

When we execute above statements we will get output like as shown below



Now we will insert default values in “SAMPLE1” table by executing following query and check values of @@identity, scope_identity() and ident_current(‘tablenae’)


INSERT SAMPLE1 DEFAULT VALUES

SELECT @@IDENTITY  -- It returns value 100 this was inserted by trigger

SELECT SCOPE_IDENTITY()  -- It returns value 1 this was inserted by insert query in SAMPLE1

SELECT IDENT_CURRENT('SAMPLE2') -- It returns value inserted in SAMPLE2 table

Our output will be like as shown below


SQL Server Difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT


If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

7 comments :

Anonymous said...

maan gaye guru

Unknown said...

Hi..

So you mean to say that Scope Identity & Currentident are same unless we specify the table name ?

Unknown said...

Hi..

So you mean to say that Scope Identity & Currentident are same unless we specify the table name ?

Anonymous said...

hi

Anonymous said...

nice sir

sunil said...

please explain in brief i did not understand.

Anonymous said...

very nice sir.

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.