Hello friends, In this tutorial, we are going to see the IDENTITY column in SQL Server. what is the difference between @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY?                                                                                                                                                                                                                             An Identity column in SQL Server is a column that stores the value generated by the SQL Server Database. This is very similar to AutoNumber in SQL Server or Sequence in Oracle.

Introduction:

An Identity column in SQL Server is a column that stores the value generated by the SQL Server Database.
This is very similar to AutoNumber in SQL Server or Sequence in Oracle.
In this tutorial, we are going to discuss different option for Identity Column.

  • @@IDENTITY.
  • IDENT_CURRENT.
  • SCOPE_IDENTITY.

Let’s discuss each of these terms in detail.

Difference between

1- @@IDENTITY:

@@IDENTITY returns last identity value generated for any table in the active sessions across all scopes.

For example:

Suppose I have two tables called table1 and table2 and I have created a trigger on table1 that will insert
record in table2 then @@IDENTITY will return the IDENTITY value of table 2.

2- CURRENT_IDENT:

It returns the last identity value generated for a specific table in any session and any scope. That means it is not affected by Scopes and Sessions it is only related to the specific table.

For example:

if I am using SCOPE_IDENTITY for table1 then it will return IDENTITY value for table1.

3- SCOPE_IDENTITY:

Returns the last identity value inserted into an identity column in the same scope. A scope can be a Stored Procedure, User Defined Function, Trigger or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

For example:

Suppose we create an insert trigger on the table which inserts a row in another table with generating an identity column, Then the Scope_IDENTITY will return the IDENTITY value of table on which the record is being inserted.

Example of @IDENTITY, CURRENT_IDENT andSCOPE_IDENTITY:

CREATE TABLE Table1(ID int IDENTITY);

CREATE TABLE Table2(ID int IDENTITY(100,1));

GO

CREATE TRIGGER trgParent ON Table1 FOR INSERT

AS

BEGIN

INSERT Table2 DEFAULT VALUES

END;

GO

--End of trigger definition

SELECT ID FROM Table1;
--id is empty.

SELECT ID FROM Table2;
--ID is empty.

--Do the following in Session 1
INSERT Table1 DEFAULT VALUES;
SELECT @@IDENTITY;
/*Returns the value 100. This was inserted by the trigger.*/

SELECT SCOPE_IDENTITY();
/* Returns the value 1. This was inserted by the
INSERT statement two statements before this query.*/

SELECT IDENT_CURRENT('Table2');

/* Returns value inserted into Table2, that is in the trigger.*/

SELECT IDENT_CURRENT('Table1');

/* Returns value inserted into Table1. 
This was the INSERT statement four statements before this query.*/

-- Do the following in Session 2.

SELECT @@IDENTITY;

/* Returns NULL because there has been no INSERT action
up to this point in this session.*/

SELECT SCOPE_IDENTITY();

/* Returns NULL because there has been no INSERT action
up to this point in this scope in this session.*/

SELECT IDENT_CURRENT('Table2');

/* Returns the last value inserted into Table2.*/

View More:

Conclusion:

I hope you understand what exactly is the difference between @@IDENTITY, IDENT_CURRENT, and SCOPE_IDENTITY.

Thank You.

42 Comments

  • I need to to thank you for this very good read!! I definitely loved every little bit of it. I have got you book-marked to check out new things you post…|

  • I enjoy looking through a post that will make people think. Also, many thanks for allowing for me to comment!|

  • I’m truly enjoying the design and layout of your blog. It’s a very easy on the eyes which makes it much more pleasant for me to come here and visit more often. Did you hire out a designer to create your theme? Superb work!

  • I’m extremely impressed along with your writing talents as neatly as with the layout for your weblog. Is that this a paid topic or did you customize it your self? Anyway stay up the nice high quality writing, it is uncommon to peer a nice weblog like this one these days..

  • whoah this blog is excellent i really like reading your articles. Keep up the good work! You recognize, lots of people are searching round for this information, you can aid them greatly.

  • I simply needed to thank you very much once more. I’m not certain the things I would’ve made to happen in the absence of the actual creative concepts discussed by you concerning this question. It actually was a very challenging condition in my position, nevertheless seeing a skilled strategy you processed the issue took me to leap for happiness. I’m just happier for the support and then sincerely hope you realize what a powerful job that you’re doing teaching most people via your blog post. I know that you’ve never come across all of us.

  • you’re truly a just right webmaster. The website loading velocity is incredible. It seems that you are doing any unique trick. Also, The contents are masterwork. you’ve done a fantastic task on this matter!

  • I loved as much as you will receive carried out right here. The sketch is attractive, your authored material stylish. nonetheless, you command get got an shakiness over that you wish be delivering the following. unwell unquestionably come further formerly again since exactly the same nearly very often inside case you shield this hike.|

  • I was wondering if you ever considered changing the layout of your website? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having one or 2 images. Maybe you could space it out better?

  • Today, I went to the beach front with my kids. I found a sea shell and gave it to my 4 year old daughter and said “You can hear the ocean if you put this to your ear.” She put the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is totally off topic but I had to tell someone!

  • Attractive element of content. I just stumbled upon your weblog and in accession capital to say that I acquire actually loved account your blog posts. Anyway I will be subscribing to your feeds or even I success you get entry to consistently quickly.

  • 472362 396609Visiting begin a business venture about the web generally indicates exposing your products or services moreover provider not only to some individuals inside your town, but however to lots of future prospects who might be more than the internet numerous times. simple internet business 852099

  • Thanks for your article. Another point is that being a photographer requires not only problem in capturing award-winning photographs and also hardships in establishing the best digicam suited to your needs and most especially hardships in maintaining the grade of your camera. It is very true and evident for those photography lovers that are in to capturing this nature’s eye-catching scenes – the mountains, the forests, the particular wild and the seas. Visiting these adventurous places undoubtedly requires a video camera that can meet the wild’s unpleasant area.

  • Thanks for your article on this site. From my own personal experience, many times softening way up a photograph may possibly provide the professional photographer with an amount of an artsy flare. Many times however, the soft clouds isn’t what precisely you had in mind and can usually spoil an otherwise good image, especially if you anticipate enlarging the item.

  • What’s Going down i am new to this, I stumbled upon this I’ve found It positively helpful and it has helped me out loads. I’m hoping to contribute & assist other customers like its helped me. Good job.|

  • Do you have a spam issue on this website; I also am a blogger, and I was wondering your situation; many of us have created some nice procedures and we are looking to trade techniques with others, please shoot me an email if interested.|

  • I simply wanted to appreciate you again. I am not sure what I would’ve done in the absence of the tips and hints shared by you regarding my area of interest. It was the frightful setting in my view, nevertheless finding out a new skilled avenue you managed the issue forced me to cry with delight. Now i’m thankful for the support and thus pray you know what a powerful job you have been doing teaching some other people using your blog. I’m certain you’ve never got to know any of us.

  • Woah! I’m really digging the template/theme of this website. It’s simple, yet effective. A lot of times it’s very difficult to get that “perfect balance” between user friendliness and visual appeal. I must say that you’ve done a amazing job with this. In addition, the blog loads extremely quick for me on Firefox. Excellent Blog!

  • 464762 525243I feel other internet site proprietors ought to take this website as an model, really clean and fantastic user friendly style and style, as well as the content. Youre an expert in this topic! 458968

  • Hello! Quick question that’s totally off topic. Do you know how to make your site mobile friendly? My website looks weird when viewing from my apple iphone. I’m trying to find a theme or plugin that might be able to resolve this issue. If you have any recommendations, please share. Thank you!

  • Greetings from Ohio! I’m bored to death at work so I decided to check out your blog on my iphone during lunch break. I love the knowledge you present here and can’t wait to take a look when I get home. I’m shocked at how fast your blog loaded on my phone .. I’m not even using WIFI, just 3G .. Anyways, great site!|

  • I have to express thanks to this writer for bailing me out of such a scenario. Because of exploring throughout the internet and seeing ways which are not powerful, I assumed my life was well over. Living devoid of the solutions to the problems you have fixed through this blog post is a crucial case, and the kind which could have negatively damaged my entire career if I had not discovered your web blog. Your personal skills and kindness in taking care of all the things was useful. I am not sure what I would have done if I had not come upon such a thing like this. I can now relish my future. Thanks a lot so much for this expert and amazing guide. I will not think twice to endorse your blog post to any individual who will need guide about this subject matter.

  • Greetings from Carolina! I’m bored to tears at work so I decided to check out your site on my iphone during lunch break. I love the info you present here and can’t wait to take a look when I get home. I’m amazed at how fast your blog loaded on my mobile .. I’m not even using WIFI, just 3G .. Anyways, awesome blog!|