Confused by SCOPE_IDENTITY() and GO

Question

I am a bit confused by the documentation & behavior of SCOPE_IDENTITY() in SQL Server.

This page https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017 says this about SCOPE_IDENTITY():

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

And it contains this example

USE AdventureWorks2012;  
GO  
INSERT INTO Person.ContactType ([Name]) VALUES ('Assistant to the Manager');  
GO  
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];  
GO  
SELECT @@IDENTITY AS [@@IDENTITY];  
GO  

Which returns

SCOPE_IDENTITY  
21  
@@IDENTITY  
21

From the docs I would have thought that the result of SCOPE_IDENTITY() would be NULL because SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]; is executed in a different batch (because it comes after GO) than the INSERT command... What am I missing here?

Answer 1

I'd agree, I think the documentation is slightly misleading. SCOPE_IDENTITY does retain its value across multiple batches directly executed on the same connection.

But note that if you create an inner batch, by executing EXEC with a string, that inner batch's SCOPE_IDENTITY is independent from your outer batch's SCOPE_IDENTITY

---

This script produces the value 2, not 5:

create table T1 (ID int IDENTITY(2,1000) not null,Val char(1))
create table T2 (ID int IDENTITY(5,1000) not null, Val char(1))
go
insert into T1(Val) values ('a')
exec('insert into T2(Val) values (''b'')')
select SCOPE_IDENTITY()