{"id":297,"date":"2014-09-08T10:12:05","date_gmt":"2014-09-08T10:12:05","guid":{"rendered":"http:\/\/mairwa.com\/wordpress\/?p=297"},"modified":"2014-09-08T10:12:05","modified_gmt":"2014-09-08T10:12:05","slug":"updated-sql-questions","status":"publish","type":"post","link":"http:\/\/mairwa.com\/wordpress\/?p=297","title":{"rendered":"Updated sql questions"},"content":{"rendered":"<p><strong>Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT<\/strong><\/p>\n<p>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:<\/p>\n<ul>\n<li>@@IDENTITY<\/li>\n<li>SCOPE_IDENTITY<\/li>\n<li>IDENT_CURRENT<\/li>\n<\/ul>\n<p>All three functions return last-generated identity values. However, the scope and session on which <i>last<\/i> is defined in each of these functions differ.<\/p>\n<p>Compare<\/p>\n<p>@@IDENTITY<\/p>\n<p>It returns the last identity value generated for any table in the current session, across all scopes.<\/p>\n<p>Let me explain this&#8230; 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.<\/p>\n<p>SCOPE_IDENTITY<\/p>\n<p>It returns the last identity value generated for any table in the current session and the current scope.<\/p>\n<p>Let me explain this&#8230; 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.<\/p>\n<p>IDENT_CURRENT<\/p>\n<p>It returns the last identity value generated for a specific table in any session and any scope.<\/p>\n<p>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.<\/p>\n<p>SQL Query<\/p>\n<p>I am explaining the above process with the help of some sample query, hope it helps:<\/p>\n<p>CREATE TABLE Parent(id int IDENTITY);<\/p>\n<div>\n<p>\u00a0CREATE TABLE Child(id int IDENTITY(100,1));<\/p>\n<p>GO<\/p>\n<p>CREATE TRIGGER Parentins ON Parent FOR INSERT<\/p>\n<p>AS<\/p>\n<p>BEGIN<\/p>\n<p>INSERT Child DEFAULT VALUES<\/p>\n<p>END;<\/p>\n<p>GO<\/p>\n<p><i>&#8211;End of trigger definition<\/i><\/p>\n<p>SELECT id FROM Parent;<i>&#8211;id is empty.<\/i><\/p>\n<p>SELECT id FROM Child;<i>&#8211;ID is empty. <\/i><\/p>\n<p><i>&#8211;Do the following in Session 1<\/i><\/p>\n<p>INSERT Parent DEFAULT VALUES;<\/p>\n<p>SELECT @@IDENTITY;<\/p>\n<p><i>\/*Returns the value 100. This was inserted by the trigger.*\/<\/i><\/p>\n<p>SELECT SCOPE_IDENTITY();<\/p>\n<p><i>\/* Returns the value 1. This was inserted by the<\/i><\/p>\n<p><i>INSERT statement two statements before this query.*\/<\/i><\/p>\n<p>SELECT IDENT_CURRENT(&#8216;Child&#8217;);<\/p>\n<p><i>\/* Returns value inserted into Child, that is in the trigger.*\/<\/i><\/p>\n<p>SELECT IDENT_CURRENT(&#8216;Parent&#8217;);<\/p>\n<p><i>\/* Returns value inserted into Parent. <\/i><\/p>\n<p><i>This was the INSERT statement four statements before this query.*\/<\/i><\/p>\n<p><i>&#8212; Do the following in Session 2.<\/i><\/p>\n<p>SELECT @@IDENTITY;<\/p>\n<p><i>\/* Returns NULL because there has been no INSERT action <\/i><i>up to this point in this session.*\/<\/i><\/p>\n<p>SELECT SCOPE_IDENTITY();<\/p>\n<p><i>\/* Returns NULL because there has been no INSERT action <\/i><i>up to this point in this scope in this session.*\/<\/i><\/p>\n<p>SELECT IDENT_CURRENT(&#8216;Child&#8217;);<\/p>\n<p><i>\/* Returns the last value inserted into Child.*\/<\/i><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p class=\"excerpt\">Difference between @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT 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&hellip; <a href=\"http:\/\/mairwa.com\/wordpress\/?p=297\">Read more &rarr;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,13,14],"tags":[],"class_list":["post-297","post","type-post","status-publish","format-standard","hentry","category-c-vb","category-sql","category-sql-server","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/297","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=297"}],"version-history":[{"count":0,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/297\/revisions"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=297"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}