{"id":21,"date":"2008-09-19T00:58:38","date_gmt":"2008-09-19T07:58:38","guid":{"rendered":"http:\/\/myplace.bluecastle.us\/2008\/9?p=21"},"modified":"2008-09-19T00:58:38","modified_gmt":"2008-09-19T07:58:38","slug":"scope_identity-returns-a-value","status":"publish","type":"post","link":"http:\/\/myplace.bluecastle.us\/?p=21","title":{"rendered":"SCOPE_IDENTITY() returns a value?"},"content":{"rendered":"

I have long used SCOPE_IDENTITY() to get the primary key of a newly inserted row immediately after the INSERT statement. For example: <\/p>\n

INSERT INTO myTable
SELET @var = SCOPE_IDENTITY() <\/code><\/p>\n

What I incorrectly assumed was that the value was reset with each statement similar to the behavior of @@ERROR. If not each statement, then each INSERT statement. In other words, [@more@]if I insert zero records then I expected SCOPE_IDENTITY to return a null value. In actuallity, both SCOPE_IDENTITY and @@IDENTITY retain thier values until another identity value is generated. <\/p>\n

As SQL Server Books Online says: <\/p>\n

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.<\/em> weblink <\/a><\/p>\n

Run the following code against a server to see. (Should work against any 2000 or 2005 server.)<\/p>\n

— ============================================<\/span>
— TEST<\/span>
 <\/span>
— select identities<\/span>
    <\/span>
SELECT<\/span> 'Before first insert:'<\/span>,<\/span> @@IDENTITY<\/span> '@@IDENTITY'<\/span>,<\/span> SCOPE_IDENTITY<\/span>()<\/span>'SCOPE_IDENTITY()'<\/span>,<\/span>@@ROWCOUNT<\/span> '@@ROWCOUNT'<\/span><\/span>
 <\/span>
— Insert 10 records into table<\/span>
INSERT<\/span> INTO<\/span> @tblTemp01 <\/span>
SELECT<\/span> TOP<\/span> 10 SPECIFIC_NAME <\/span>
FROM<\/span> INFORMATION_SCHEMA.ROUTINES<\/span> <\/span>
 <\/span>
— select identities<\/span>
SELECT<\/span> 'After first insert:'<\/span>,<\/span> @@IDENTITY<\/span> '@@IDENTITY'<\/span>,<\/span> SCOPE_IDENTITY<\/span>()<\/span>'SCOPE_IDENTITY()'<\/span>,<\/span>@@ROWCOUNT<\/span> '@@ROWCOUNT'<\/span><\/span>
 <\/span>
— Insert 0 records into table<\/span>
INSERT<\/span> INTO<\/span> @tblTemp01<\/span>
SELECT<\/span> TOP<\/span> 10 SPECIFIC_NAME <\/span>
FROM<\/span> INFORMATION_SCHEMA.ROUTINES<\/span> <\/span>
WHERE<\/span> SPECIFIC_NAME =<\/span> 'A_very_unlikely_name_to_RETURn_0_rows'<\/span><\/span>
 <\/span>
— select identities<\/span>
SELECT<\/span> 'After second insert:'<\/span>,<\/span> @@IDENTITY<\/span> '@@IDENTITY'<\/span>,<\/span> SCOPE_IDENTITY<\/span>()<\/span>'SCOPE_IDENTITY()'<\/span>,<\/span>@@ROWCOUNT<\/span> '@@ROWCOUNT'<\/span><\/span>
 <\/span>
— select from table<\/span>
SELECT<\/span> *<\/span> FROM<\/span> @tblTemp01<\/span> <\/p>\n

 <\/p>\n

aldkjfkljasd;lkfj a;ldkjf  a;lkdsfj<\/p>\n

Which returns this: (look closely at the 'after 2cnd insert')<\/div>\n
 <\/div>\n
 <\/div>\n
 <\/div>\n
 <\/div>\n","protected":false},"excerpt":{"rendered":"

I have long used SCOPE_IDENTITY() to get the primary key of a newly inserted row immediately after the INSERT statement. For example: INSERT INTO myTableSELET @var = SCOPE_IDENTITY() What I incorrectly assumed was that the value was reset with each statement similar to the behavior of @@ERROR. If not each statement, then each INSERT statement. In other words, [@more@]if I… Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,8,9],"tags":[],"class_list":["post-21","post","type-post","status-publish","format-standard","hentry","category-code","category-software","category-sql","xfolkentry"],"_links":{"self":[{"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=\/wp\/v2\/posts\/21","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=21"}],"version-history":[{"count":0,"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=\/wp\/v2\/posts\/21\/revisions"}],"wp:attachment":[{"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=21"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=21"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/myplace.bluecastle.us\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=21"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}