{"id":351,"date":"2011-08-27T22:45:23","date_gmt":"2011-08-28T02:45:23","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=351"},"modified":"2014-11-13T14:03:46","modified_gmt":"2014-11-13T19:03:46","slug":"identity-crisis","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/","title":{"rendered":"Identity Crisis"},"content":{"rendered":"

\nI wanted to write about this one for a while now but never got around to it. Today I came across a Connect<\/a> issue that reminded me of this topic again.\n<\/p>\n

\nIf you have bee around SQL Server for a while you propably have seen this function:
\n[sql]SELECT @@IDENTITY AS LastIdentityValue[\/sql]\nIt allows you to retrieve the last identity value generated during the last insert that was executed in your connection.
\nThis can be very useful when you have to insert a new row in a table with an identity column primary key and you also have to insert rows that reference the new row into a child table.
\n
\nThe problem with @@IDENTITY<\/code> is, that it actually does exactly what it says it does: It returns the most recent identity value that was generated for your connection. That means, if you insert into a table with an identity column and that insert fires a trigger in which an insert into another table with an identity column gets executed, @@IDENTITY<\/code> will return the identity value generated for that second insert statement in that is part of the trigger. This value is most likely not the one that you want.\n<\/p>\n

\nNow you might think, that you do not have triggers and therefore you do not have a problem here. Think about what happens if your boss tomorrow tells the new employee to write some auditing code. That colleague of yours might use triggers that insert into auditing tables with an identity column. Suddenly your code starts producing wrong values. These are the kinds of hard to find bugs that will cost you some long nights debugging.\n<\/p>\n

\nSo you probably want to not use @@IDENTITY<\/code> in your code — ever. But what alternative is available?\n<\/p>\n

\nThere is the IDENT_CURRENT()<\/code> function:
\n[sql]SELECT IDENT_CURRENT('dbo.MyTable') AS LastIdentityValue[\/sql]\nIt takes a table name as parameter and returns the last identity value generated for that table.
\n
\nBut that is probably not the value you are after either: IDENT_CURRENT()<\/code> does not take the connection into account. So if you are in a highly concurrent environment, you will get values returned that are far off the value that was generated for your connection.\n<\/p>\n

\nBut there is hope:
\n[sql]SELECT SCOPE_IDENTITY() AS LastIdentityValue[\/sql]\nSCOPE_IDENTITY()<\/code> returns the last identity value generated for your connection and within the current scope. That means that identity values generated for anything else like other connections or even triggers in the same connections do not affect the value returned by SCOPE_IDENTITY()<\/code>.\n<\/p>\n

\nThe following code example demonstrates the different values returned by the three functions.
\n[sql]\nIF OBJECT_ID('dbo.IdentityTest') IS NOT NULL DROP TABLE dbo.IdentityTest;
\nIF OBJECT_ID('dbo.IdentityTestForTrigger') IS NOT NULL DROP TABLE dbo.IdentityTestForTrigger;
\nGO
\nCREATE TABLE dbo.IdentityTest(id INT IDENTITY(1,1));
\nCREATE TABLE dbo.IdentityTestForTrigger(id INT IDENTITY(13,1));
\nGO
\nCREATE TRIGGER dbo.IdentityTestTrigger ON dbo.IdentityTest AFTER INSERT
\nAS
\nBEGIN
\n INSERT INTO dbo.IdentityTestForTrigger DEFAULT VALUES;
\nEND;
\nGO<\/p>\n

INSERT INTO dbo.IdentityTest DEFAULT VALUES;<\/p>\n

WAITFOR DELAY '00:00:10';<\/p>\n

SELECT @@IDENTITY AS [@@IDENTITY],IDENT_CURRENT('dbo.IdentityTest') AS [IDENT_CURRENT('dbo.IdentityTest')],SCOPE_IDENTITY() AS [SCOPE_IDENTITY()];<\/p>\n[\/sql]\nWithin ten seconds of executing this code, run the following code in a different connection:
\n[sql]\nINSERT INTO dbo.IdentityTest DEFAULT VALUES;
\nINSERT INTO dbo.IdentityTest DEFAULT VALUES;
\nINSERT INTO dbo.IdentityTest DEFAULT VALUES;
\n[\/sql]\n<\/p>\n

\nOnly SCOPE_IDENITYT()<\/code> returns the desired value 1. @@IDENTITY<\/code> returns 13 and IDENT_CURRENT()<\/code> returns 4 — both are values that we are not looking for.\n<\/p>\n

\nBut before you now start to jubilate as the problem seems solved, hold on a minute: SCOPE_IDENITYT()<\/code> does not work!\n<\/p>\n

\nThe details of the problem you can find in KB 2019779<\/a>. There is a connect item<\/a> referenced in this article. This item is marked as fixed, however there is no information given which version(s) of SQL Server received the fix.\n<\/p>\n

\nSo I can not recommend to use SCOPE_IDENITYT()<\/code> either.\n<\/p>\n

\nInstead you can use the OUTPUT<\/code> clause:
\n[sql]\nDECLARE @IdentValues TABLE(v INT);<\/p>\n

INSERT INTO dbo.IdentityTest
\nOUTPUT INSERTED.id INTO @IdentValues(v)
\nDEFAULT VALUES;<\/p>\n

SELECT v AS IdentityValues FROM @IdentValues;
\n[\/sql]\nThis code snippet assumes you ran the previous example to setup the tables and the trigger.\n<\/p>\n

\nThis method works without any (known) problems. It also allows you to get to all identity values generated during this insert, not only the last one. However, if you need just the last one, replace the select statement in the example with this one:
\n[sql]\nSELECT MAX(v) AS LastIdentityValue FROM @IdentValues;
\n[\/sql]\n<\/p>\n

\nThere is one additional benefit of this method: If you go ahead next year and change all your identity columns to use the new Denali feature SEQUENCE<\/code>, this method will continue to work unchanged:
\n[sql]\nIF OBJECT_ID('dbo.SequenceTable') IS NOT NULL DROP TABLE dbo.SequenceTable;
\nIF OBJECT_ID('dbo.TestSequence') IS NOT NULL DROP SEQUENCE dbo.TestSequence;
\nGO
\nCREATE SEQUENCE dbo.TestSequence AS INT;
\nCREATE TABLE dbo.SequenceTable (id INT DEFAULT NEXT VALUE FOR dbo.TestSequence);
\nGO
\nDECLARE @IdentValues TABLE(v INT);<\/p>\n

INSERT INTO dbo.SequenceTable
\nOUTPUT INSERTED.id INTO @IdentValues(v)
\nDEFAULT VALUES;<\/p>\n

SELECT v AS IdentityValues FROM @IdentValues;
\n[\/sql]\n","protected":false},"excerpt":{"rendered":"

I wanted to write about this one for a while now but never got around to it. Today I came across a Connect issue that reminded me of this topic again. If you have bee around SQL Server for a [more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5,14],"tags":[],"class_list":["post-351","post","type-post","status-publish","format-standard","hentry","category-general","category-sql-server-internals"],"acf":[],"yoast_head":"\nIdentity Crisis<\/title>\n<meta name=\"description\" content=\"This article describes how to get the value of the last generated identity value in several contexts and demonstrates their shortcomings. It provides a solution that even works with Denali's new sequences.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/351\/identity-crisis\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Identity Crisis\" \/>\n<meta property=\"og:description\" content=\"This article describes how to get the value of the last generated identity value in several contexts and demonstrates their shortcomings. It provides a solution that even works with Denali's new sequences.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/351\/identity-crisis\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2011-08-28T02:45:23+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T19:03:46+00:00\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"Identity Crisis\",\"datePublished\":\"2011-08-28T02:45:23+00:00\",\"dateModified\":\"2014-11-13T19:03:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/\"},\"wordCount\":842,\"commentCount\":1,\"articleSection\":[\"General\",\"SQL Server Internals\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/\",\"name\":\"Identity Crisis\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2011-08-28T02:45:23+00:00\",\"dateModified\":\"2014-11-13T19:03:46+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"This article describes how to get the value of the last generated identity value in several contexts and demonstrates their shortcomings. It provides a solution that even works with Denali's new sequences.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/351\\\/identity-crisis\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Identity Crisis\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Identity Crisis","description":"This article describes how to get the value of the last generated identity value in several contexts and demonstrates their shortcomings. It provides a solution that even works with Denali's new sequences.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/","og_locale":"en_US","og_type":"article","og_title":"Identity Crisis","og_description":"This article describes how to get the value of the last generated identity value in several contexts and demonstrates their shortcomings. It provides a solution that even works with Denali's new sequences.","og_url":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2011-08-28T02:45:23+00:00","article_modified_time":"2014-11-13T19:03:46+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"Identity Crisis","datePublished":"2011-08-28T02:45:23+00:00","dateModified":"2014-11-13T19:03:46+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/"},"wordCount":842,"commentCount":1,"articleSection":["General","SQL Server Internals"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/351\/identity-crisis\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/","url":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/","name":"Identity Crisis","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2011-08-28T02:45:23+00:00","dateModified":"2014-11-13T19:03:46+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"This article describes how to get the value of the last generated identity value in several contexts and demonstrates their shortcomings. It provides a solution that even works with Denali's new sequences.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/351\/identity-crisis\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/351\/identity-crisis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Identity Crisis"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-5F","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/351","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=351"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/351\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=351"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=351"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=351"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}