<p>How am I supposed to get the <code>IDENTITY</code> of an inserted row?</p>
<p>I know about <code>@@IDENTITY</code> and <code>IDENT_CURRENT</code> and <code>SCOPE_IDENTITY</code>, but don't understand the implications or impacts attached to each.</p>
<p>Can someone please explain the differences and when I would be using each?</p>

[{'answer_id': 42652, 'author': 'Jakub Šturc', 'author_id': 2361, 'author_profile': 'https://Stackoverflow.com/users/2361', 'pm_score': 5, 'selected': False, 'text': '<p>From MSDN</p>\n<blockquote>\n<p>@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.</p>\n<p>@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.</p>\n<p>IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.</p>\n</blockquote>\n<ul>\n<li>IDENT_CURRENT is a function which takes a table as a argument.</li>\n<li>@@IDENTITY may return confusing result when you have an trigger on the table</li>\n<li>SCOPE_IDENTITY is your hero most of the time.</li>\n</ul>\n'}, {'answer_id': 42655, 'author': 'bdukes', 'author_id': 2688, 'author_profile': 'https://Stackoverflow.com/users/2688', 'pm_score': 12, 'selected': True, 'text': '<ul>\n<li><p><a href="http://msdn.microsoft.com/en-us/library/ms187342.aspx" rel="noreferrer"><code>@@IDENTITY</code></a> returns the last identity value generated for any table in the current session, across all scopes.  <strong>You need to be careful here</strong>, since it\'s across scopes.  You could get a value from a trigger, instead of your current statement.</p></li>\n<li><p><a href="http://msdn.microsoft.com/en-us/library/ms190315.aspx" rel="noreferrer"><code>SCOPE_IDENTITY()</code></a> returns the last identity value generated for any table in the current session and the current scope.  <strong>Generally what you want to use</strong>.</p></li>\n<li><p><a href="http://msdn.microsoft.com/en-us/library/ms175098.aspx" rel="noreferrer"><code>IDENT_CURRENT(\'tableName\')</code></a> returns the last identity value generated for a specific table in any session and any scope.  This lets you specify which table you want the value from, in case the two above aren\'t quite what you need (<strong>very rare</strong>).  Also, as @<a href="https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row#42665">Guy Starbuck</a> mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."</p></li>\n<li><p>The <a href="http://msdn.microsoft.com/en-us/library/ms177564.aspx" rel="noreferrer"><code>OUTPUT</code> clause</a> of the <code>INSERT</code> statement will let you access every row that was inserted via that statement.  Since it\'s scoped to the specific statement, it\'s <strong>more straightforward</strong> than the other functions above.  However, it\'s a little <strong>more verbose</strong> (you\'ll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back.  That said, if your query uses a parallel execution plan, this is the <strong>only guaranteed method</strong> for getting the identity (short of turning off parallelism). However, it is executed <em>before</em> triggers and cannot be used to return trigger-generated values.</p></li>\n</ul>\n'}, {'answer_id': 42665, 'author': 'Guy Starbuck', 'author_id': 2194, 'author_profile': 'https://Stackoverflow.com/users/2194', 'pm_score': 4, 'selected': False, 'text': "<p><strong>@@IDENTITY</strong> is the last identity inserted using the current SQL Connection.  This is a good value to return from an insert stored procedure, where you just need the identity inserted for your new record, and don't care if more rows were added afterward.</p>\n\n<p><strong>SCOPE_IDENTITY</strong> is the last identity inserted using the current SQL Connection, and in the current scope -- that is, if there was a second IDENTITY inserted based on a trigger after your insert, it would not be reflected in SCOPE_IDENTITY, only the insert you performed. Frankly, I have never had a reason to use this.</p>\n\n<p><strong>IDENT_CURRENT(tablename)</strong> is the last identity inserted regardless of connection or scope.  You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into.</p>\n"}, {'answer_id': 42669, 'author': 'Orion Edwards', 'author_id': 234, 'author_profile': 'https://Stackoverflow.com/users/234', 'pm_score': 7, 'selected': False, 'text': "<p>I'm saying the same thing as the other guys, so everyone's correct, I'm just trying to make it more clear.</p>\n\n<p><code>@@IDENTITY</code> returns the id of the last thing that was inserted by your client's connection to the database.<br>\nMost of the time this works fine, but sometimes a trigger will go and insert a new row that you don't know about, and you'll get the ID from this new row, instead of the one you want</p>\n\n<p><code>SCOPE_IDENTITY()</code> solves this problem. It returns the id of the last thing that <em>you inserted</em> in the SQL code <em>you sent</em> to the database. If triggers go and create extra rows, they won't cause the wrong value to get returned. Hooray</p>\n\n<p><code>IDENT_CURRENT</code> returns the last ID that was inserted by anyone. If some other app happens to insert another row at an unforunate time, you'll get the ID of that row instead of your one.</p>\n\n<p>If you want to play it safe, always use <code>SCOPE_IDENTITY()</code>. If you stick with <code>@@IDENTITY</code> and someone decides to add a trigger later on, all your code will break.</p>\n"}, {'answer_id': 1545927, 'author': 'erikkallen', 'author_id': 47161, 'author_profile': 'https://Stackoverflow.com/users/47161', 'pm_score': 4, 'selected': False, 'text': "<p><strong>ALWAYS</strong> use scope_identity(), there's NEVER a need for anything else.</p>\n"}, {'answer_id': 6073578, 'author': 'Orry', 'author_id': 70454, 'author_profile': 'https://Stackoverflow.com/users/70454', 'pm_score': 8, 'selected': False, 'text': '<p>I believe the safest and most accurate method of retrieving the inserted id would be  using the output clause.</p>\n<p>for example (taken from the following <a href="http://msdn.microsoft.com/en-us/library/ms177564.aspx" rel="noreferrer">MSDN</a> article)</p>\n<blockquote>\n<pre><code>USE AdventureWorks2008R2;\nGO\nDECLARE @MyTableVar table( NewScrapReasonID smallint,\n                           Name varchar(50),\n                           ModifiedDate datetime);\nINSERT Production.ScrapReason\n    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate\n        INTO @MyTableVar\nVALUES (N\'Operator error\', GETDATE());\n\n--Display the result set of the table variable.\nSELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;\n--Display the result set of the table.\nSELECT ScrapReasonID, Name, ModifiedDate \nFROM Production.ScrapReason;\nGO\n</code></pre>\n</blockquote>\n'}, {'answer_id': 16273230, 'author': 'Ian Kemp', 'author_id': 70345, 'author_profile': 'https://Stackoverflow.com/users/70345', 'pm_score': 6, 'selected': False, 'text': "<p>The best (read: safest) way to get the identity of a newly-inserted row is by using the <code>output</code> clause:</p>\n\n<pre><code>create table TableWithIdentity\n           ( IdentityColumnName int identity(1, 1) not null primary key,\n             ... )\n\n-- type of this table's column must match the type of the\n-- identity column of the table you'll be inserting into\ndeclare @IdentityOutput table ( ID int )\n\ninsert TableWithIdentity\n     ( ... )\noutput inserted.IdentityColumnName into @IdentityOutput\nvalues\n     ( ... )\n\nselect @IdentityValue = (select ID from @IdentityOutput)\n</code></pre>\n"}, {'answer_id': 29353469, 'author': 'Jim', 'author_id': 4546567, 'author_profile': 'https://Stackoverflow.com/users/4546567', 'pm_score': 5, 'selected': False, 'text': '<p>Add</p>\n\n<pre><code>SELECT CAST(scope_identity() AS int);\n</code></pre>\n\n<p>to the end of your insert sql statement, then</p>\n\n<pre><code>NewId = command.ExecuteScalar()\n</code></pre>\n\n<p>will retrieve it.</p>\n'}, {'answer_id': 40425907, 'author': 'Ian Boyd', 'author_id': 12597, 'author_profile': 'https://Stackoverflow.com/users/12597', 'pm_score': 5, 'selected': False, 'text': '<p>When you use Entity Framework, it internally uses the <code>OUTPUT</code> technique to return the newly inserted ID value</p>\n\n<pre><code>DECLARE @generated_keys table([Id] uniqueidentifier)\n\nINSERT INTO TurboEncabulators(StatorSlots)\nOUTPUT inserted.TurboEncabulatorID INTO @generated_keys\nVALUES(\'Malleable logarithmic casing\');\n\nSELECT t.[TurboEncabulatorID ]\nFROM @generated_keys AS g \n   JOIN dbo.TurboEncabulators AS t \n   ON g.Id = t.TurboEncabulatorID \nWHERE @@ROWCOUNT &gt; 0\n</code></pre>\n\n<p>The output results are stored in a temporary table variable, joined back to the table, and return the row value out of the table.</p>\n\n<p>Note: I have no idea why EF would inner join the ephemeral table back to the real table (under what circumstances would the two not match).</p>\n\n<p>But that\'s what EF does. </p>\n\n<p>This technique (<code>OUTPUT</code>) is only available on SQL Server 2008 or newer.</p>\n\n<h2><strong>Edit</strong> - The reason for the join</h2>\n\n<p>The reason that Entity Framework joins back to the original table, rather than simply use the <code>OUTPUT</code> values is because EF also uses this technique to get the <code>rowversion</code> of a newly inserted row. </p>\n\n<p>You can use optimistic concurrency in your entity framework models by <a href="https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/handling-concurrency-with-the-entity-framework-in-an-asp-net-mvc-application" rel="noreferrer">using the <code>Timestamp</code> attribute:</a> <sup><a href="https://archive.ph/u3SC6" rel="noreferrer"></a></sup></p>\n\n<pre><code>public class TurboEncabulator\n{\n   public String StatorSlots)\n\n   [Timestamp]\n   public byte[] RowVersion { get; set; }\n}\n</code></pre>\n\n<p>When you do this, Entity Framework will need the <code>rowversion</code> of the newly inserted row:</p>\n\n<pre><code>DECLARE @generated_keys table([Id] uniqueidentifier)\n\nINSERT INTO TurboEncabulators(StatorSlots)\nOUTPUT inserted.TurboEncabulatorID INTO @generated_keys\nVALUES(\'Malleable logarithmic casing\');\n\nSELECT t.[TurboEncabulatorID], t.[RowVersion]\nFROM @generated_keys AS g \n   JOIN dbo.TurboEncabulators AS t \n   ON g.Id = t.TurboEncabulatorID \nWHERE @@ROWCOUNT &gt; 0\n</code></pre>\n\n<p>And in order to retrieve this <code>Timetsamp</code> you <strong>cannot</strong> use an <code>OUTPUT</code> clause. </p>\n\n<p>That\'s because if there\'s a trigger on the table, any <code>Timestamp</code> you OUTPUT will be wrong:</p>\n\n<ul>\n<li>Initial insert. Timestamp: 1</li>\n<li>OUTPUT clause outputs timestamp: 1</li>\n<li>trigger modifies row. Timestamp: 2</li>\n</ul>\n\n<p>The returned timestamp will <strong>never</strong> be correct if you have a trigger on the table. So you <strong>must</strong> use a separate <code>SELECT</code>.</p>\n\n<p>And even if you were willing to suffer the incorrect rowversion, the other reason to perform a separate <code>SELECT</code> is that you cannot OUTPUT a <code>rowversion</code> into a table variable:</p>\n\n<pre><code>DECLARE @generated_keys table([Id] uniqueidentifier, [Rowversion] timestamp)\n\nINSERT INTO TurboEncabulators(StatorSlots)\nOUTPUT inserted.TurboEncabulatorID, inserted.Rowversion INTO @generated_keys\nVALUES(\'Malleable logarithmic casing\');\n</code></pre>\n\n<p>The third reason to do it is for symmetry. When performing an <code>UPDATE</code> on a table with a trigger, you <strong>cannot</strong> use an <code>OUTPUT</code> clause. Trying do <code>UPDATE</code> with an <code>OUTPUT</code> is not supported, and will give an error:</p>\n\n<ul>\n<li><a href="https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table">Cannot use UPDATE with OUTPUT clause when a trigger is on the table</a></li>\n</ul>\n\n<p>The only way to do it is with a follow-up <code>SELECT</code> statement:</p>\n\n<pre><code>UPDATE TurboEncabulators\nSET StatorSlots = \'Lotus-O deltoid type\'\nWHERE ((TurboEncabulatorID = 1) AND (RowVersion = 792))\n\nSELECT RowVersion\nFROM TurboEncabulators\nWHERE @@ROWCOUNT &gt; 0 AND TurboEncabulatorID = 1\n</code></pre>\n'}, {'answer_id': 48040418, 'author': 'Khan Ataur Rahman', 'author_id': 6880332, 'author_profile': 'https://Stackoverflow.com/users/6880332', 'pm_score': -1, 'selected': False, 'text': "<p>After Your Insert Statement you need to add this. And Make sure about the table name where data is inserting.You will get current row no where row affected just now by your insert statement.  </p>\n\n<pre><code>IDENT_CURRENT('tableName')\n</code></pre>\n"}, {'answer_id': 50725760, 'author': 'MarredCheese', 'author_id': 5405967, 'author_profile': 'https://Stackoverflow.com/users/5405967', 'pm_score': 4, 'selected': False, 'text': "<p>I can't speak to other versions of SQL Server, but in 2012, outputting directly works just fine.  You don't need to bother with a temporary table.</p>\n\n<pre><code>INSERT INTO MyTable\nOUTPUT INSERTED.ID\nVALUES (...)\n</code></pre>\n\n<p>By the way, this technique also works when inserting multiple rows.</p>\n\n<pre><code>INSERT INTO MyTable\nOUTPUT INSERTED.ID\nVALUES\n    (...),\n    (...),\n    (...)\n</code></pre>\n\n<p>Output</p>\n\n<pre><code>ID\n2\n3\n4\n</code></pre>\n"}, {'answer_id': 55142419, 'author': 'Frank Roth', 'author_id': 1073330, 'author_profile': 'https://Stackoverflow.com/users/1073330', 'pm_score': 2, 'selected': False, 'text': "<p>Create a <code>uuid</code> and also insert it to a column. Then you can easily identify your row with the uuid. Thats the only 100% working solution you can implement. All the other solutions are too complicated or are not working in same edge cases.\nE.g.:</p>\n\n<p><strong>1) Create row</strong></p>\n\n<pre><code>INSERT INTO table (uuid, name, street, zip) \n        VALUES ('2f802845-447b-4caa-8783-2086a0a8d437', 'Peter', 'Mainstreet 7', '88888');\n</code></pre>\n\n<p><strong>2) Get created row</strong></p>\n\n<pre><code>SELECT * FROM table WHERE uuid='2f802845-447b-4caa-8783-2086a0a8d437';\n</code></pre>\n"}, {'answer_id': 59266753, 'author': 'Andy Robertson', 'author_id': 664210, 'author_profile': 'https://Stackoverflow.com/users/664210', 'pm_score': 2, 'selected': False, 'text': "<p>One other way to guarantee the identity of the rows you insert is to specify the identity values and use the <code>SET IDENTITY_INSERT ON</code> and then <code>OFF</code>. This guarantees you know exactly what the identity values are! As long as the values are not in use then you can insert these values into the identity column.</p>\n\n<pre><code>CREATE TABLE #foo \n  ( \n     fooid   INT IDENTITY NOT NULL, \n     fooname VARCHAR(20) \n  ) \n\nSELECT @@Identity            AS [@@Identity], \n       Scope_identity()      AS [SCOPE_IDENTITY()], \n       Ident_current('#Foo') AS [IDENT_CURRENT] \n\nSET IDENTITY_INSERT #foo ON \n\nINSERT INTO #foo \n            (fooid, \n             fooname) \nVALUES      (1, \n             'one'), \n            (2, \n             'Two') \n\nSET IDENTITY_INSERT #foo OFF \n\nSELECT @@Identity            AS [@@Identity], \n       Scope_identity()      AS [SCOPE_IDENTITY()], \n       Ident_current('#Foo') AS [IDENT_CURRENT] \n\nINSERT INTO #foo \n            (fooname) \nVALUES      ('Three') \n\nSELECT @@Identity            AS [@@Identity], \n       Scope_identity()      AS [SCOPE_IDENTITY()], \n       Ident_current('#Foo') AS [IDENT_CURRENT] \n\n-- YOU CAN INSERT  \nSET IDENTITY_INSERT #foo ON \n\nINSERT INTO #foo \n            (fooid, \n             fooname) \nVALUES      (10, \n             'Ten'), \n            (11, \n             'Eleven') \n\nSET IDENTITY_INSERT #foo OFF \n\nSELECT @@Identity            AS [@@Identity], \n       Scope_identity()      AS [SCOPE_IDENTITY()], \n       Ident_current('#Foo') AS [IDENT_CURRENT] \n\nSELECT * \nFROM   #foo \n</code></pre>\n\n<p>This can be a very useful technique if you are loading data from another source or merging data from two databases etc.</p>\n"}, {'answer_id': 60305940, 'author': 'StevenJe', 'author_id': 9195980, 'author_profile': 'https://Stackoverflow.com/users/9195980', 'pm_score': 1, 'selected': False, 'text': '<p>Even though this is an older thread, there is a newer way to do this which avoids some of the pitfalls of the IDENTITY column in older versions of SQL Server, <a href="https://dba.stackexchange.com/questions/88930/identity-value-jumps-when-restarting-sql-server">like gaps in the identity values after server reboots</a>. Sequences are available in SQL Server 2016 and forward which is the newer way is to create a SEQUENCE object using TSQL.  This allows you create your own numeric sequence object in SQL Server and control how it increments. </p>\n\n<p>Here is an example: </p>\n\n<pre><code>CREATE SEQUENCE CountBy1  \n    START WITH 1  \n    INCREMENT BY 1 ;  \nGO  \n</code></pre>\n\n<p>Then in TSQL you would do the following to get the next sequence ID:</p>\n\n<pre><code>SELECT NEXT VALUE FOR CountBy1 AS SequenceID\nGO\n</code></pre>\n\n<p>Here are the links to <a href="https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2016" rel="nofollow noreferrer">CREATE SEQUENCE</a> and <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-2016" rel="nofollow noreferrer">NEXT VALUE FOR</a></p>\n'}]
['https://Stackoverflow.com/questions/42648', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1583/']