{"id":4301,"date":"2024-10-21T06:07:47","date_gmt":"2024-10-20T22:07:47","guid":{"rendered":"https:\/\/five.co\/?p=4301"},"modified":"2024-10-21T06:07:49","modified_gmt":"2024-10-20T22:07:49","slug":"sql-scope-identity","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-scope-identity\/","title":{"rendered":"SQL SCOPE_IDENTITY(): Comprehensive Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL SCOPE_IDENTITY()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In SQL Server accurately retrieving the last inserted identity value is crucial for many database operations. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Enter SCOPE_IDENTITY(), a function that provides a reliable way to fetch this value within the current execution scope. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This article delves into the intricacies of SCOPE_IDENTITY(), exploring its usage, benefits, and how it compares to alternatives.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-t936l wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-t936l \"><div class=\"eb-toc-container eb-toc-t936l  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL SCOPE_IDENTITY()&quot;,&quot;text&quot;:&quot;Breakdown of SQL SCOPE_IDENTITY()&quot;,&quot;link&quot;:&quot;breakdown-of-sql-scope_identity&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Understanding SCOPE_IDENTITY()&quot;,&quot;text&quot;:&quot;Understanding SCOPE_IDENTITY()&quot;,&quot;link&quot;:&quot;understanding-scope_identity&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;SCOPE_IDENTITY() vs. @@IDENTITY&quot;,&quot;text&quot;:&quot;SCOPE_IDENTITY() vs. @@IDENTITY&quot;,&quot;link&quot;:&quot;scope_identity-vs-identity&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;SCOPE_IDENTITY() in Stored Procedures&quot;,&quot;text&quot;:&quot;SCOPE_IDENTITY() in Stored Procedures&quot;,&quot;link&quot;:&quot;scope_identity-in-stored-procedures&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;SCOPE_IDENTITY() and Transactions&quot;,&quot;text&quot;:&quot;SCOPE_IDENTITY() and Transactions&quot;,&quot;link&quot;:&quot;scope_identity-and-transactions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;SCOPE_IDENTITY() in Multi-Table Inserts&quot;,&quot;text&quot;:&quot;SCOPE_IDENTITY() in Multi-Table Inserts&quot;,&quot;link&quot;:&quot;scope_identity-in-multi-table-inserts&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;SCOPE_IDENTITY() in Dynamic SQL&quot;,&quot;text&quot;:&quot;SCOPE_IDENTITY() in Dynamic SQL&quot;,&quot;link&quot;:&quot;scope_identity-in-dynamic-sql&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Applications of SCOPE_IDENTITY()&quot;,&quot;text&quot;:&quot;Real-World Applications of SCOPE_IDENTITY()&quot;,&quot;link&quot;:&quot;real-world-applications-of-scope_identity&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;E-commerce Order Processing&quot;,&quot;text&quot;:&quot;E-commerce Order Processing&quot;,&quot;link&quot;:&quot;e-commerce-order-processing&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Content Management System&quot;,&quot;text&quot;:&quot;Content Management System&quot;,&quot;link&quot;:&quot;content-management-system&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Audit Trail System&quot;,&quot;text&quot;:&quot;Audit Trail System&quot;,&quot;link&quot;:&quot;audit-trail-system&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion&quot;,&quot;text&quot;:&quot;Conclusion&quot;,&quot;link&quot;:&quot;conclusion&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL SCOPE_IDENTITY()&quot;,&quot;value&quot;:&quot;breakdown-of-sql-scope_identity&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Understanding SCOPE_IDENTITY()&quot;,&quot;value&quot;:&quot;understanding-scope_identity&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;SCOPE_IDENTITY() vs. @@IDENTITY&quot;,&quot;value&quot;:&quot;scope_identity-vs-identity&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;SCOPE_IDENTITY() in Stored Procedures&quot;,&quot;value&quot;:&quot;scope_identity-in-stored-procedures&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;SCOPE_IDENTITY() and Transactions&quot;,&quot;value&quot;:&quot;scope_identity-and-transactions&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;SCOPE_IDENTITY() in Multi-Table Inserts&quot;,&quot;value&quot;:&quot;scope_identity-in-multi-table-inserts&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;SCOPE_IDENTITY() in Dynamic SQL&quot;,&quot;value&quot;:&quot;scope_identity-in-dynamic-sql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Applications of SCOPE_IDENTITY()&quot;,&quot;value&quot;:&quot;real-world-applications-of-scope_identity&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;E-commerce Order Processing&quot;,&quot;value&quot;:&quot;e-commerce-order-processing&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Content Management System&quot;,&quot;value&quot;:&quot;content-management-system&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Audit Trail System&quot;,&quot;value&quot;:&quot;audit-trail-system&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Conclusion&quot;,&quot;value&quot;:&quot;conclusion&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#breakdown-of-sql-scope_identity\">Breakdown of SQL SCOPE_IDENTITY()<\/a><li><a href=\"#understanding-scope_identity\">Understanding SCOPE_IDENTITY()<\/a><li><a href=\"#scope_identity-vs-identity\">SCOPE_IDENTITY() vs. @@IDENTITY<\/a><li><a href=\"#scope_identity-in-stored-procedures\">SCOPE_IDENTITY() in Stored Procedures<\/a><li><a href=\"#scope_identity-and-transactions\">SCOPE_IDENTITY() and Transactions<\/a><li><a href=\"#scope_identity-in-multi-table-inserts\">SCOPE_IDENTITY() in Multi-Table Inserts<\/a><li><a href=\"#scope_identity-in-dynamic-sql\">SCOPE_IDENTITY() in Dynamic SQL<\/a><li><a href=\"#real-world-applications-of-scope_identity\">Real-World Applications of SCOPE_IDENTITY()<\/a><li><a href=\"#conclusion\">Conclusion<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Understanding SCOPE_IDENTITY()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SCOPE_IDENTITY() is a SQL Server function that returns the last identity value inserted into an identity column within the current execution scope. But what does &#8220;execution scope&#8221; mean? It&#8217;s the current query or stored procedure being executed, including any triggers that might be fired as a result.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s look at a basic example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"INSERT INTO Customers (Name, Email)\nVALUES ('John Doe', 'john@example.com');\n\nSELECT SCOPE_IDENTITY() AS LastInsertedID;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Customers (<\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\">, Email)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;John Doe&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;john@example.com&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> LastInsertedID;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this snippet, SCOPE_IDENTITY() will return the ID of the newly inserted customer. Simple, right? But its true value becomes apparent in more complex scenarios.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SCOPE_IDENTITY() vs. @@IDENTITY<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">You might wonder, &#8220;Why not just use @@IDENTITY?&#8221; While @@IDENTITY also returns the last inserted identity value, it does so across all scopes in the current session. This can lead to unexpected results, especially when triggers are involved.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Consider this scenario:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TABLE Orders (\n    OrderID INT IDENTITY(1,1) PRIMARY KEY,\n    CustomerID INT,\n    OrderDate DATE\n);\n\nCREATE TABLE OrderAudit (\n    AuditID INT IDENTITY(1,1) PRIMARY KEY,\n    OrderID INT,\n    AuditDate DATETIME\n);\n\nCREATE TRIGGER tr_Orders_Insert\nON Orders\nAFTER INSERT\nAS\nBEGIN\n    INSERT INTO OrderAudit (OrderID, AuditDate)\n    SELECT OrderID, GETDATE()\n    FROM inserted;\nEND;\n\n-- Now, let's insert an order\nINSERT INTO Orders (CustomerID, OrderDate)\nVALUES (1, GETDATE());\n\n-- Compare the results\nSELECT SCOPE_IDENTITY() AS Scope_Identity,\n       @@IDENTITY AS Identity,\n       IDENT_CURRENT('Orders') AS Ident_Current;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Orders<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">IDENTITY<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    CustomerID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderDate <\/span><span style=\"color: #C678DD\">DATE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">OrderAudit<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    AuditID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">IDENTITY<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    OrderID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    AuditDate <\/span><span style=\"color: #C678DD\">DATETIME<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRIGGER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">tr_Orders_Insert<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AFTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INSERT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> OrderAudit (OrderID, AuditDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> OrderID, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">()<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> inserted;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Now, let&#39;s insert an order<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Orders (CustomerID, OrderDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">());<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Compare the results<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> Scope_Identity,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       @@<\/span><span style=\"color: #C678DD\">IDENTITY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">Identity<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       <\/span><span style=\"color: #56B6C2\">IDENT_CURRENT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;Orders&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> Ident_Current;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, SCOPE_IDENTITY() will return the OrderID from the Orders table, while @@IDENTITY will return the AuditID from the OrderAudit table (inserted by the trigger). SCOPE_IDENTITY() gives you the value you&#8217;re likely looking for in most scenarios.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SCOPE_IDENTITY() in Stored Procedures<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SCOPE_IDENTITY() shines in stored procedures, especially when multiple inserts occur. It always returns the identity from the procedure&#8217;s scope, not from any nested scopes (like triggers).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s an example stored procedure:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE PROCEDURE CreateOrderWithItems\n    @CustomerID INT,\n    @ItemID1 INT,\n    @ItemID2 INT\nAS\nBEGIN\n    DECLARE @OrderID INT;\n\n    INSERT INTO Orders (CustomerID, OrderDate)\n    VALUES (@CustomerID, GETDATE());\n\n    SET @OrderID = SCOPE_IDENTITY();\n\n    INSERT INTO OrderItems (OrderID, ItemID)\n    VALUES (@OrderID, @ItemID1),\n           (@OrderID, @ItemID2);\n\n    SELECT @OrderID AS NewOrderID;\nEND;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PROCEDURE<\/span><span style=\"color: #ABB2BF\"> CreateOrderWithItems<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@CustomerID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@ItemID1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@ItemID2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Orders (CustomerID, OrderDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@CustomerID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">());<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">();<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> OrderItems (OrderID, ItemID)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@ItemID1<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">           (<\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@ItemID2<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> NewOrderID;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this procedure, SCOPE_IDENTITY() captures the OrderID immediately after the Orders insert, ensuring we have the correct ID for the OrderItems inserts, regardless of any triggers that might fire.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SCOPE_IDENTITY() and Transactions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SCOPE_IDENTITY() works reliably within transactions, maintaining its scope even if the transaction is rolled back. This behavior is particularly useful in error-handling scenarios:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"BEGIN TRY\n    BEGIN TRANSACTION;\n\n    INSERT INTO Customers (Name, Email)\n    VALUES ('Jane Smith', 'jane@example.com');\n\n    DECLARE @NewCustomerID INT = SCOPE_IDENTITY();\n\n    -- Simulating an error\n    IF @NewCustomerID % 2 = 0\n        THROW 50000, 'Even ID encountered', 1;\n\n    INSERT INTO Orders (CustomerID, OrderDate)\n    VALUES (@NewCustomerID, GETDATE());\n\n    COMMIT TRANSACTION;\nEND TRY\nBEGIN CATCH\n    ROLLBACK TRANSACTION;\n    PRINT 'Error occurred. Transaction rolled back.';\n    PRINT 'Last inserted Customer ID (even though rolled back): ' + CAST(SCOPE_IDENTITY() AS VARCHAR(10));\nEND CATCH;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">BEGIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRANSACTION<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Customers (<\/span><span style=\"color: #C678DD\">Name<\/span><span style=\"color: #ABB2BF\">, Email)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;Jane Smith&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;jane@example.com&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewCustomerID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">();<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Simulating an error<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">IF<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewCustomerID<\/span><span style=\"color: #ABB2BF\"> % <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">THROW<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">50000<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Even ID encountered&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Orders (CustomerID, OrderDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@NewCustomerID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">());<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">COMMIT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRANSACTION<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRY<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">CATCH<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">ROLLBACK<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRANSACTION<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">PRINT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Error occurred. Transaction rolled back.&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">PRINT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Last inserted Customer ID (even though rolled back): &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">() <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">));<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">CATCH<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, even if the transaction is rolled back due to the simulated error, SCOPE_IDENTITY() still returns the last inserted ID within its scope.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SCOPE_IDENTITY() in Multi-Table Inserts<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When working with related tables, SCOPE_IDENTITY() helps maintain data integrity by ensuring you&#8217;re using the correct identity value. Here&#8217;s an example with a parent-child relationship:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TABLE Departments (\n    DepartmentID INT IDENTITY(1,1) PRIMARY KEY,\n    DepartmentName NVARCHAR(50)\n);\n\nCREATE TABLE Employees (\n    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,\n    DepartmentID INT,\n    EmployeeName NVARCHAR(100),\n    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)\n);\n\n-- Insert a new department and employees\nDECLARE @NewDeptID INT;\n\nINSERT INTO Departments (DepartmentName)\nVALUES ('Research and Development');\n\nSET @NewDeptID = SCOPE_IDENTITY();\n\nINSERT INTO Employees (DepartmentID, EmployeeName)\nVALUES (@NewDeptID, 'Alice Johnson'),\n       (@NewDeptID, 'Bob Williams');\n\nSELECT d.DepartmentName, e.EmployeeName\nFROM Departments d\nJOIN Employees e ON d.DepartmentID = e.DepartmentID\nWHERE d.DepartmentID = @NewDeptID;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Departments<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    DepartmentID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">IDENTITY<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    DepartmentName <\/span><span style=\"color: #C678DD\">NVARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">50<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">Employees<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    EmployeeID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">IDENTITY<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">PRIMARY KEY<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    DepartmentID <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    EmployeeName <\/span><span style=\"color: #C678DD\">NVARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">100<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FOREIGN KEY<\/span><span style=\"color: #ABB2BF\"> (DepartmentID) <\/span><span style=\"color: #C678DD\">REFERENCES<\/span><span style=\"color: #ABB2BF\"> Departments(DepartmentID)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Insert a new department and employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewDeptID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Departments (DepartmentName)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;Research and Development&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewDeptID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">();<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Employees (DepartmentID, EmployeeName)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@NewDeptID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Alice Johnson&#39;<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">       (<\/span><span style=\"color: #E06C75\">@NewDeptID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Bob Williams&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentName<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">EmployeeName<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> Departments d<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">JOIN<\/span><span style=\"color: #ABB2BF\"> Employees e <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">e<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentID<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">d<\/span><span style=\"color: #ABB2BF\">.<\/span><span style=\"color: #D19A66\">DepartmentID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewDeptID<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This script creates a new department and immediately uses its ID to insert related employees, ensuring data consistency.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SCOPE_IDENTITY() in Dynamic SQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When using dynamic SQL, SCOPE_IDENTITY() behaves differently. It only returns the last identity value from the immediate scope where it&#8217;s called, not from the dynamic SQL scope. Here&#8217;s how to work around this:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DECLARE @TableName NVARCHAR(128) = 'Customers';\nDECLARE @SQL NVARCHAR(MAX);\nDECLARE @NewID INT;\n\nSET @SQL = N'\n    INSERT INTO ' + QUOTENAME(@TableName) + ' (Name, Email)\n    VALUES (''Dynamic Insert'', ''dynamic@example.com'');\n\n    SET @NewIDOUT = SCOPE_IDENTITY();\n';\n\nEXEC sp_executesql @SQL, N'@NewIDOUT INT OUTPUT', @NewIDOUT = @NewID OUTPUT;\n\nPRINT 'New ID from dynamic SQL: ' + CAST(@NewID AS VARCHAR(10));\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@TableName<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NVARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">128<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;Customers&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@SQL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NVARCHAR<\/span><span style=\"color: #ABB2BF\">(MAX);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@SQL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> N<\/span><span style=\"color: #98C379\">&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #98C379\">    INSERT INTO &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #56B6C2\">QUOTENAME<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #E06C75\">@TableName<\/span><span style=\"color: #ABB2BF\">) + <\/span><span style=\"color: #98C379\">&#39; (Name, Email)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #98C379\">    VALUES (&#39;&#39;Dynamic Insert&#39;&#39;, &#39;&#39;dynamic@example.com&#39;&#39;);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #98C379\">    SET @NewIDOUT = SCOPE_IDENTITY();<\/span><\/span>\n<span class=\"line\"><span style=\"color: #98C379\">&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">EXEC<\/span><span style=\"color: #ABB2BF\"> sp_executesql <\/span><span style=\"color: #E06C75\">@SQL<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">N&#39;@NewIDOUT INT OUTPUT&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@NewIDOUT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OUTPUT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">PRINT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;New ID from dynamic SQL: &#39;<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #56B6C2\">CAST<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #E06C75\">@NewID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">));<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">By using an output parameter, we can capture the SCOPE_IDENTITY() value from within the dynamic SQL scope.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Applications of SCOPE_IDENTITY()<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s explore some practical scenarios where SCOPE_IDENTITY() proves invaluable:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">E-commerce Order Processing<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In an e-commerce system, you often need to create an order and immediately associate it with order items:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE PROCEDURE CreateOrder\n    @CustomerID INT,\n    @Items OrderItemType READONLY -- User-defined table type\nAS\nBEGIN\n    DECLARE @OrderID INT;\n\n    INSERT INTO Orders (CustomerID, OrderDate, Status)\n    VALUES (@CustomerID, GETDATE(), 'Pending');\n\n    SET @OrderID = SCOPE_IDENTITY();\n\n    INSERT INTO OrderItems (OrderID, ProductID, Quantity, Price)\n    SELECT @OrderID, ProductID, Quantity, Price\n    FROM @Items;\n\n    -- Calculate and update order total\n    UPDATE Orders\n    SET TotalAmount = (SELECT SUM(Quantity * Price) FROM OrderItems WHERE OrderID = @OrderID)\n    WHERE OrderID = @OrderID;\n\n    SELECT @OrderID AS NewOrderID;\nEND;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PROCEDURE<\/span><span style=\"color: #ABB2BF\"> CreateOrder<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@CustomerID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@Items<\/span><span style=\"color: #ABB2BF\"> OrderItemType <\/span><span style=\"color: #C678DD\">READONLY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #7F848E; font-style: italic\">-- User-defined table type<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Orders (CustomerID, OrderDate, <\/span><span style=\"color: #C678DD\">Status<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@CustomerID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">(), <\/span><span style=\"color: #98C379\">&#39;Pending&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">();<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> OrderItems (OrderID, ProductID, Quantity, Price)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\">, ProductID, Quantity, Price<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@Items<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Calculate and update order total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UPDATE<\/span><span style=\"color: #ABB2BF\"> Orders<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> TotalAmount <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(Quantity * Price) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> OrderItems <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> OrderID <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> OrderID <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@OrderID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> NewOrderID;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This procedure creates an order, inserts order items, and updates the total amount, all using the OrderID obtained from SCOPE_IDENTITY().<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Content Management System<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">In a CMS, you might want to create a new article and immediately tag it:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE PROCEDURE CreateArticleWithTags\n    @Title NVARCHAR(200),\n    @Content NTEXT,\n    @AuthorID INT,\n    @Tags NVARCHAR(MAX) -- Comma-separated list of tags\nAS\nBEGIN\n    DECLARE @ArticleID INT;\n\n    INSERT INTO Articles (Title, Content, AuthorID, PublishDate)\n    VALUES (@Title, @Content, @AuthorID, GETDATE());\n\n    SET @ArticleID = SCOPE_IDENTITY();\n\n    -- Split and insert tags\n    INSERT INTO ArticleTags (ArticleID, TagName)\n    SELECT @ArticleID, value\n    FROM STRING_SPLIT(@Tags, ',');\n\n    SELECT @ArticleID AS NewArticleID;\nEND;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PROCEDURE<\/span><span style=\"color: #ABB2BF\"> CreateArticleWithTags<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@Title<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NVARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">200<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@Content<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NTEXT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@AuthorID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #E06C75\">@Tags<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NVARCHAR<\/span><span style=\"color: #ABB2BF\">(MAX) <\/span><span style=\"color: #7F848E; font-style: italic\">-- Comma-separated list of tags<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@ArticleID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> Articles (Title, Content, AuthorID, PublishDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #E06C75\">@Title<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@Content<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@AuthorID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">());<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@ArticleID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">();<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #7F848E; font-style: italic\">-- Split and insert tags<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> ArticleTags (ArticleID, TagName)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@ArticleID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #C678DD\">value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">STRING_SPLIT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #E06C75\">@Tags<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@ArticleID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> NewArticleID;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This procedure creates a new article and immediately associates it with the provided tags, using SCOPE_IDENTITY() to get the new article&#8217;s ID.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Audit Trail System<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When implementing an audit trail, you often need to record the ID of the newly inserted record:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CREATE TRIGGER tr_Employees_Insert\nON Employees\nAFTER INSERT\nAS\nBEGIN\n    DECLARE @NewEmployeeID INT = SCOPE_IDENTITY();\n\n    INSERT INTO AuditTrail (TableName, RecordID, Action, AuditDate)\n    VALUES ('Employees', @NewEmployeeID, 'Insert', GETDATE());\nEND;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TRIGGER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">tr_Employees_Insert<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> Employees<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AFTER<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INSERT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">AS<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@NewEmployeeID<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">SCOPE_IDENTITY<\/span><span style=\"color: #ABB2BF\">();<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> AuditTrail (TableName, RecordID, <\/span><span style=\"color: #C678DD\">Action<\/span><span style=\"color: #ABB2BF\">, AuditDate)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;Employees&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #E06C75\">@NewEmployeeID<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;Insert&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">GETDATE<\/span><span style=\"color: #ABB2BF\">());<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This trigger uses SCOPE_IDENTITY() to get the ID of the newly inserted employee and records it in the audit trail.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SCOPE_IDENTITY() is a function in SQL Server for accurately retrieving the last inserted identity value within the current execution scope. Its scope-aware behavior makes it more reliable than alternatives like @@IDENTITY, especially in complex scenarios involving triggers or multiple inserts.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">By using SCOPE_IDENTITY(), you ensure that your code retrieves the correct identity values, maintaining data integrity and consistency in your database operations. Whether you&#8217;re building an e-commerce platform, a content management system, or implementing audit trails, SCOPE_IDENTITY() provides a dependable way to work with identity columns.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL SCOPE_IDENTITY() In SQL Server accurately retrieving the last inserted identity value is crucial for many database operations. Enter SCOPE_IDENTITY(), a function that provides a reliable way to fetch this value within the current execution scope. This article delves into the intricacies of SCOPE_IDENTITY(), exploring its usage, benefits, and how it compares to [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4305,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4301","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-SCOPE_IDENTITY-Comprehensive-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of SQL SCOPE_IDENTITY() In SQL Server accurately retrieving the last inserted identity value is crucial for many database operations. Enter SCOPE_IDENTITY(), a function that provides a reliable way to fetch this value within the current execution scope. This article delves into the intricacies of SCOPE_IDENTITY(), exploring its usage, benefits, and how it compares to&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4301","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4301"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4301\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4305"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4301"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}