#CodeProject Latest articles - All Topics CodeProject Latest articles - Artificial Intelligence CodeProject Lounge Postings CodeProject Click here to Skip to main content 14,409,784 members Sign in [login32.png] Email ____________________ Password ____________________ [BUTTON Input] (not implemented)____ Sign in Forgot your password? __________________________________________________________________ Sign in with [octicons_github.png] [linkedin.png] [facebook.png] [google-plus.png] [microsoft.png] Home [SQL-Server&sz=728x90&c=68609] ____________________ [search.gif]-Submit Search within: (*) Articles ( ) Quick Answers ( ) Messages * home * articles + Chapters and Sections> o + Latest Articles + Top Articles + Posting/Update Guidelines + Article Help Forum + [write13.png] Submit an article or tip + [write13.png] Import GitHub Project + [write13.png] Import your Blog + * quick answersQ&A + [write13.png] Ask a Question about this article + [write13.png] Ask a Question + View Unanswered Questions + View All Questions + View C# questions + View VB.NET questions + View ASP.NET questions + View SQL questions + View Javascript questions + * discussionsforums + All Message Boards... + Application Lifecycle> o Running a Business o Sales / Marketing o Collaboration / Beta Testing o Work Issues + Design and Architecture + ASP.NET + JavaScript + C / C++ / MFC> o ATL / WTL / STL o Managed C++/CLI + C# + Free Tools + Objective-C and Swift + Database + Hardware & Devices> o System Admin + Hosting and Servers + Java + Linux Programming + .NET Framework + Android + iOS + Mobile + SharePoint + Silverlight / WPF + Visual Basic + Web Development + Site Bugs / Suggestions + Spam and Abuse Watch + * featuresstuff + Competitions + News + The Insider Newsletter + The Daily Build Newsletter + Newsletter archive + Surveys + Product Showcase + CodeProject Stuff + * communitylounge + Who's Who + Most Valuable Professionals + The Lounge + Where I Am: Member Photos + The Insider News + The Weird & The Wonderful + + Non-English Language > o General Indian Topics + * help? + What is 'CodeProject'? + General FAQ + Ask a Question + Bugs and Suggestions + Article Help Forum + About Us + Articles » Database » Database » SQL Server [print48.png] Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT [{3362c29a-b27d-4b3c-bb39-ccac81edc868}.jpg] Samrat Banerjee loading... Rate this: [stars-fill-lg.png] [stars-empty-lg.png] 4.64 (44 votes) (*) ( ) ( ) ( ) ( ) Please Sign up or sign in to vote. 4.64 (44 votes) [loading24.gif] Vote! 22 Aug 2010CPOL Difference between SQL @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT functions * Download query - 3.39 KB Introduction In most of our application scenario, we need to get latest inserted row information through SQL query. And for that, we have multiple options like: * @@IDENTITY * SCOPE_IDENTITY * IDENT_CURRENT All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ. Compare @@IDENTITY It returns the last identity value generated for any table in the current session, across all scopes. Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger. SCOPE_IDENTITY It returns the last identity value generated for any table in the current session and the current scope. Let me explain this... suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function. IDENT_CURRENT It returns the last identity value generated for a specific table in any session and any scope. In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope. SQL Query I am explaining the above process with the help of some sample query, hope it helps: CREATE TABLE Parent(id int IDENTITY); CREATE TABLE Child(id int IDENTITY(100,1)); GO CREATE TRIGGER Parentins ON Parent FOR INSERT AS BEGIN INSERT Child DEFAULT VALUES END; GO --End of trigger definition SELECT id FROM Parent; --id is empty. SELECT id FROM Child; --ID is empty. --Do the following in Session 1 INSERT Parent 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('Child'); /* Returns value inserted into Child, that is in the trigger.*/ SELECT IDENT_CURRENT('Parent'); /* Returns value inserted into Parent. 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('Child'); /* Returns the last value inserted into Child.*/ History * 22^nd August, 2010: Initial post [SQL-Server&sz=300x250&c=68609] License This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL) Share * * * * * * About the Author [{3362c29a-b27d-4b3c-bb39-ccac81edc868}.jpg] Samrat Banerjee Software Developer (Senior) India India Hi, I am Samrat Banerjee from India. I am a Software Engineer working in .net platform. I love to explore new technologies. [SQL-Server&sz=728x90&c=68609] Comments and Discussions First Prev Next Question Comments Pin bikah chanda mohanta27-Apr-17 5:19 Member bikah chanda mohanta 27-Apr-17 5:19 Nice article Answer @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT Pin cauvin_stephane@yahoo.fr5-Nov-16 6:02 Member cauvin_stephane@yahoo.fr 5-Nov-16 6:02 Thank you for your explanations Best regards General My vote of 5 Pin neetesh12319-Oct-15 2:49 Member neetesh123 19-Oct-15 2:49 excellent. Question Your commnet iw wrong Pin redc dccdc4-Oct-15 1:39 Member redc dccdc 4-Oct-15 1:39 please verify it then post Question Good Article Pin Member 23341727-Oct-14 20:49 Member Member 2334172 7-Oct-14 20:49 Good General Good Artical Pin ramprasad addanki7-Jan-14 18:52 Member ramprasad addanki 7-Jan-14 18:52 Nice explanation.. Thanks. it helps me a lot Question Good explanation Pin rohit kakria18-Mar-12 22:14 Member rohit kakria 18-Mar-12 22:14 It is more elaborated than an article. Keep it up. Regards, Rohit Xpode.com General My vote of 3 Pin JayantiChauhan15-Mar-12 3:25 Member JayantiChauhan 15-Mar-12 3:25 Gud Example General My vote of 1 Pin pinaldave24-Aug-10 12:06 Member pinaldave 24-Aug-10 12:06 not clear General My vote of 3 Pin Bishoy Demian24-Aug-10 0:10 Member Bishoy Demian 24-Aug-10 0:10 not clear enough General Very Good Pin Akhteruzzaman23-Aug-10 5:19 Member Akhteruzzaman 23-Aug-10 5:19 Keep it up... Thumbs Up | :thumbsup: General Re: Very Good Pin Member 23341727-Oct-14 20:41 Member Member 2334172 7-Oct-14 20:41 Go to Parent Correct General My vote of 5 Pin linuxjr23-Aug-10 3:49 professional linuxjr 23-Aug-10 3:49 Nice Explanation General My vote of 5 Pin pzokarkar22-Aug-10 23:30 Member pzokarkar 22-Aug-10 23:30 Very Nice Information About SQL Identity General My vote of 5 Pin Rahul Khadikar22-Aug-10 19:58 Member Rahul Khadikar 22-Aug-10 19:58 Nice explanation. Surly deserve 5 points. General More of a blog post Pin Not Active21-Aug-10 13:03 mentor Not Active 21-Aug-10 13:03 Although this is good information it's really more of a blog posting than an article __________________________________________________________________ I know the language. I've read a book. - _Madmatt General Re: More of a blog post Pin kornakar22-Aug-10 23:12 Member kornakar 22-Aug-10 23:12 Go to Parent Indeed this is more of a Tip&Trick than an article (a good one nontheless). Last Visit: 6-Jan-20 0:06 Last Update: 6-Jan-20 0:06 Refresh 1 General General News News Suggestion Suggestion Question Question Bug Bug Answer Answer Joke Joke Praise Praise Rant Rant Admin Admin Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages. Article View Stats Revisions (2) Comments (17) Posted 22 Aug 2010 Tagged as SQL Stats 261.1K views 677 downloads 46 bookmarked [SQL-Server&sz=160x200&c=537384] [SQL-Server&sz=160x600&c=537384] Go to top Permalink Advertise Privacy Cookies Terms of Use Layout: fixed | fluid Article Copyright 2010 by Samrat Banerjee Everything else Copyright © CodeProject, 1999-2020 Web05 2.8.191213.1 [DFPAudiencePixel;ord='637138839636603874';dc_seg=33705370?]