#Stack Overflow Feed for question 'What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?' Stack Overflow 1. Products 2. Customers 3. Use cases 1. Stack Overflow Public questions and answers 2. Teams Private questions and answers for your team 3. Enterprise Private self-hosted questions and answers for your enterprise 4. Talent Hire technical talent 5. Advertising Reach developers worldwide ____________________ Loading… 1. 2. Log in Sign up 3. current community + Stack Overflow help chat + Meta Stack Overflow your communities Sign up or log in to customize your list. more stack exchange communities company blog By using our site, you acknowledge that you have read and understand our Cookie Policy, Privacy Policy, and our Terms of Service. 1. Home 2. 1. Public 2. Stack Overflow 3. Tags 4. Users 5. Jobs 3. 1. Teams What’s this? 2. First 25 Users Free Teams Q&A for Work Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. Learn more What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()? Ask Question Asked 10 years ago Active 1 year, 5 months ago Viewed 119k times (BUTTON) 173 (BUTTON) (BUTTON) 56 I know Scope_Identity(), Identity(), @@Identity, and Ident_Current() all get the value of the identity column, but I would love to know the difference. Part of the controversy I'm having is what do they mean by scope as applied to these functions above? I would also love a simple example of different scenarios of using them? sql sql-server identity share|improve this question edited Jul 30 '17 at 13:59 robinCTS 5,15188 gold badges2323 silver badges3535 bronze badges asked Dec 17 '09 at 9:42 TeboTebo 12.4k1111 gold badges4444 silver badges6161 bronze badges * 2 Don't forget about the parallel execution bug that exists in SQL Server for SCOPE_IDENTITY and @@IDENTITY: support.microsoft.com/default.aspx?scid=kb;en-US;2019779 – David d C e Freitas Mar 29 '11 at 11:50 * @DaviddCeFreitas -- I'm curious to read about the bug, but the link appears to be broken (or at least, it's throwing an ASP error). – rory.ap Jul 7 '15 at 20:19 * 2 Actually, I found it: support.microsoft.com/en-us/kb/2019779 – rory.ap Jul 7 '15 at 20:20 * @rory.ap broken link – andrepaulo Jan 23 '17 at 22:51 * 1 @andrepaulo Link works for me. – robinCTS Jul 30 '17 at 13:53 add a comment | 8 Answers 8 active oldest votes (BUTTON) 355 (BUTTON) * The @@identity function returns the last identity created in the same session. * The scope_identity() function returns the last identity created in the same session and the same scope. * The ident_current(name) returns the last identity created for a specific table or view in any session. * The identity() function is not used to get an identity, it's used to create an identity in a select...into query. The session is the database connection. The scope is the current query or the current stored procedure. A situation where the scope_identity() and the @@identity functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity() function will return the identity created by the query, while the @@identity function will return the identity created by the trigger. So, normally you would use the scope_identity() function. share|improve this answer answered Dec 17 '09 at 9:58 GuffaGuffa 592k8484 gold badges616616 silver badges905905 bronze badges * 13 I chose this as the answer, because of the "A situation where the scope_identity() and the @@identity..." paragraph. It clarified things more. – Tebo Dec 17 '09 at 10:14 * 1 As David Freitas mentioned above, there is a bug in the implementation of scope_identity, so I recommend to use an alternative method, the OUTPUT clause. See my answer below. – Sebastian Meine Oct 30 '12 at 21:15 * @Guffa - "The session is the database connection". Is session maintained across connections if you are using Connection Pooling? – Dave Black Jan 11 '16 at 15:19 * 1 This is a role model answer. In particular, working with SQL and SQL Server can be weird, and this explains things in a very clear, layman's manner, while still being fairly informative. It doesn't sound like something being communicated between two database specialists, which a LOT of other SE answers do. – Panzercrisis Feb 13 '18 at 18:55 add a comment | (BUTTON) 41 (BUTTON) Good question. * @@IDENTITY: returns the last identity value generated on your SQL connection (SPID). Most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to an INSERT, and the trigger executes another INSERT statement). * SCOPE_IDENTITY(): returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc). * IDENT_CURRENT(): returns the last identity value for a specific table. Don't use this to get the identity value from an INSERT, it's subject to race conditions (i.e. multiple connections inserting rows on the same table). * IDENTITY(): used when declaring a column in a table as an identity column. For more reference, see: http://msdn.microsoft.com/en-us/library/ms187342.aspx. To summarize: if you are inserting rows, and you want to know the value of the identity column for the row you just inserted, always use SCOPE_IDENTITY(). share|improve this answer edited May 22 '17 at 10:55 Liam 19.9k1616 gold badges8686 silver badges137137 bronze badges answered Dec 17 '09 at 9:48 BrannonBrannon 23.1k55 gold badges3535 silver badges4242 bronze badges add a comment | (BUTTON) 14 (BUTTON) If you understand the difference between scope and session then it will be very easy to understand these methods. A very nice blog post by Adam Anderson describes this difference: Session means the current connection that's executing the command. Scope means the immediate context of a command. Every stored procedure call executes in its own scope, and nested calls execute in a nested scope within the calling procedure's scope. Likewise, a SQL command executed from an application or SSMS executes in its own scope, and if that command fires any triggers, each trigger executes within its own nested scope. Thus the differences between the three identity retrieval methods are as follows: @@identity returns the last identity value generated in this session but any scope. scope_identity() returns the last identity value generated in this session and this scope. ident_current() returns the last identity value generated for a particular table in any session and any scope. share|improve this answer edited Jul 30 '17 at 13:16 robinCTS 5,15188 gold badges2323 silver badges3535 bronze badges answered Oct 11 '11 at 6:28 Hemant SaktaHemant Sakta 18722 silver badges77 bronze badges add a comment | (BUTTON) 12 (BUTTON) Scope means the code context that performs the INSERT statement SCOPE_IDENTITY(), as opposed to the global scope of @@IDENTITY. CREATE TABLE Foo( ID INT IDENTITY(1,1), Dummy VARCHAR(100) ) CREATE TABLE FooLog( ID INT IDENTITY(2,2), LogText VARCHAR(100) ) go CREATE TRIGGER InsertFoo ON Foo AFTER INSERT AS BEGIN INSERT INTO FooLog (LogText) VALUES ('inserted Foo') INSERT INTO FooLog (LogText) SELECT Dummy FROM inserted END INSERT INTO Foo (Dummy) VALUES ('x') SELECT SCOPE_IDENTITY(), @@IDENTITY Gives different results. share|improve this answer edited Jul 30 '17 at 13:50 robinCTS 5,15188 gold badges2323 silver badges3535 bronze badges answered Dec 17 '09 at 10:11 deviodevio 34k66 gold badges7070 silver badges132132 bronze badges add a comment | (BUTTON) 9 (BUTTON) Because of the bug mentioned by @David Freitas and because of the incompatibility to the new Sequence feature that was introduced in 2012 I would recommend staying away from all three of these. Instead, you can use the OUTPUT clause to get the inserted identity value. The other advantage is that OUTPUT even works if you have inserted more than one row. For details and examples see here: Identity Crisis share|improve this answer edited Aug 25 '17 at 2:26 Horaciux 4,40922 gold badges1717 silver badges3636 bronze badges answered Oct 30 '12 at 21:12 Sebastian MeineSebastian Meine 9,5782121 silver badges3030 bronze badges * I think this answer deserves more attention. – cheeze Sep 16 '18 at 1:18 add a comment | (BUTTON) 6 (BUTTON) To clarify the problem with @@Identity: For instance, if you insert a table and that table has triggers doing inserts, @@Identity will return the id from the insert in the trigger (a log_id or something), while scope_identity() will return the id from the insert in the original table. So if you don't have any triggers, scope_identity() and @@identity will return the same value. If you have triggers, you need to think about what value you'd like. share|improve this answer edited Jul 30 '17 at 11:16 Ali 2,67344 gold badges2727 silver badges4343 bronze badges answered Dec 17 '09 at 9:51 Jonas LincolnJonas Lincoln 8,72977 gold badges3131 silver badges4646 bronze badges add a comment | (BUTTON) 4 (BUTTON) Scope Identity: Identity of last record added within the stored procedure being executed. @@Identity: Identity of last record added within the query batch, or as a result of the query e.g. a procedure that performs an insert, the then fires a trigger that then inserts a record will return the identity of the inserted record from the trigger. IdentCurrent: The last identity allocated for the table. share|improve this answer edited Jul 30 '17 at 13:46 Ali 2,67344 gold badges2727 silver badges4343 bronze badges answered Dec 17 '09 at 9:49 AndrewAndrew 23.2k44 gold badges5555 silver badges8181 bronze badges add a comment | (BUTTON) 3 (BUTTON) Here is another good explanation from the book: As for the difference between SCOPE_IDENTITY and @@IDENTITY, suppose that you have a stored procedure P1 with three statements: - An INSERT that generates a new identity value - A call to a stored procedure P2 that also has an INSERT statement that generates a new identity value - A statement that queries the functions SCOPE_IDENTITY and @@IDENTITY The SCOPE_IDENTITY function will return the value generated by P1 (same session and scope). The @@IDENTITY function will return the value generated by P2 (same session irrespective of scope). share|improve this answer answered Jan 12 '16 at 20:37 Dmitriy DokshinDmitriy Dokshin 61555 silver badges2121 bronze badges add a comment | Highly active question. Earn 10 reputation in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity. Not the answer you're looking for? Browse other questions tagged sql sql-server identity or ask your own question. Blog * This veteran started a code bootcamp for people who went to bootcamp * This Week #StackOverflowKnows Parties With Introverts, Perfect Cookie Ratio,… Featured on Meta * Update: an agreement with Monica Cellio * 2019: a year in moderation Linked 0 What is the difference of Scope_Identity and @@Identity 1 What is the difference between newsequentialid, scope_identity and @@identity? 0 PHP PDO Return Last ID Using Select 3 Get newly inserted record ID in CFScript 3 TSQL - select inserted rows 1 using @@identity when a trigger is fired in response to an INSERT, and the trigger executes another INSERT statement 1 get id from select in sql 1 How to write thread safe SQL Server stored procedures 3 New Row into SQL Server with Data Set [asp.net, c#] 0 Get the identity of an insert inside a trigger see more linked questions… Related 4528 What is the difference between “INNER JOIN” and “OUTER JOIN”? 1070 Best way to get identity of inserted row? 1364 What is the difference between UNION and UNION ALL? 1306 What is the difference between varchar and nvarchar? 935 Difference between JOIN and INNER JOIN 160 How to change identity column values programmatically? 896 When should I use cross apply over inner join? 1084 Why does comparing strings using either '==' or 'is' sometimes produce a different result? 599 What is the difference between == and equals() in Java? Hot Network Questions * Eevee is using my CPU to render * Invertible bimodules and projectivity * Why should 'boneheaded' exceptions not be caught, especially in server code? * Is walking pneumonia aka mycoplasma pneumoniae curable? * Tree Shape on a Tidally Locked World * If I burn my food, will I consume less calories as I have already burnt them? * Why does a knight combine better with the queen than a bishop? * What do "function of" and "differentiate with respect to" mean? * Installing Paclets in Mathematica online? * Why does online check-in sometimes close earlier than the offline check-in? * What is a blown out background? * How to check completeness of a bought set? * I did [chown -r root /usr/share/applications] when i just intended to only chown * The idiom used when a bad situation is not fully resolved and can escalate easily again * Why use two 0.1 µF capacitors in parallel? * Bush fires and heat waves, the real mechanics? * Meaning of つかみどころがない on a person * Return or yield from a function that calls a generator? * A passive sensor does not need any additional energy source? * Getting a fimbriated plot * Gentzen's result on PA * What is the first recorded dog name? * Generate a Nine-Ball Pool rack * Does casting Fog Cloud break a Sanctuary spell? more hot questions Question feed Subscribe to RSS Question feed To subscribe to this RSS feed, copy and paste this URL into your RSS reader. https://stackoverflo lang-sql Stack Overflow * Questions * Jobs * Developer Jobs Directory * Salary Calculator * Help * Mobile Products * Teams * Talent * Advertising * Enterprise Company * About * Press * Work Here * Legal * Privacy Policy * Contact Us Stack Exchange Network * Technology * Life / Arts * Culture / Recreation * Science * Other * Stack Overflow * Server Fault * Super User * Web Applications * Ask Ubuntu * Webmasters * Game Development * TeX - LaTeX * Software Engineering * Unix & Linux * Ask Different (Apple) * WordPress Development * Geographic Information Systems * Electrical Engineering * Android Enthusiasts * Information Security * Database Administrators * Drupal Answers * SharePoint * User Experience * Mathematica * Salesforce * ExpressionEngine® Answers * Stack Overflow em Português * Blender * Network Engineering * Cryptography * Code Review * Magento * Software Recommendations * Signal Processing * Emacs * Raspberry Pi * Stack Overflow на русском * Code Golf * Stack Overflow en español * Ethereum * Data Science * Arduino * Bitcoin * Software Quality Assurance & Testing * Sound Design * Windows Phone * more (27) * Photography * Science Fiction & Fantasy * Graphic Design * Movies & TV * Music: Practice & Theory * Worldbuilding * Video Production * Seasoned Advice (cooking) * Home Improvement * Personal Finance & Money * Academia * Law * Physical Fitness * Gardening & Landscaping * Parenting * more (11) * English Language & Usage * Skeptics * Mi Yodeya (Judaism) * Travel * Christianity * English Language Learners * Japanese Language * Chinese Language * French Language * German Language * Biblical Hermeneutics * History * Spanish Language * Islam * Русский язык * Russian Language * Arqade (gaming) * Bicycles * Role-playing Games * Anime & Manga * Puzzling * Motor Vehicle Maintenance & Repair * Board & Card Games * Bricks * Homebrewing * Martial Arts * The Great Outdoors * Poker * Chess * Sports * more (16) * MathOverflow * Mathematics * Cross Validated (stats) * Theoretical Computer Science * Physics * Chemistry * Biology * Computer Science * Philosophy * Linguistics * Psychology & Neuroscience * Computational Science * more (8) * Meta Stack Exchange * Stack Apps * API * Data * Blog * Facebook * Twitter * LinkedIn site design / logo © 2020 Stack Exchange Inc; user contributions licensed under cc by-sa 4.0 with attribution required. rev 2020.1.3.35708 Stack Overflow works best with JavaScript enabled