Session ID in SQLCMD running multi-batch script

Question

My script contains multiple GO keywords. I know that SQLCMD interprets GO as a batch delimiter, so the code is not run as a single block but instead each part between the delimiters (a batch) is run separately. My question is, are all batches executed in the same session?

I tried testing this using a simple script like this:

SELECT @@SPID
GO

SELECT @@SPID
GO

SELECT @@SPID
GO

I then ran the script from the command line using this command

SQLCMD -S MyServer\Instance -E -i MyScript.sql

and got this output:

------
    62

(1 rows affected)

------
    62

(1 rows affected)

------
    62

(1 rows affected)

It seemed to answer my question in the affirmative, but then I ran the script again and got exactly the same output, i.e. all values were 62 again. Apparently the two separate executions of SQLCMD could not possibly run in the same session, they just happened to receive the same session ID. But that in turn makes me think that the same @@SPID value return by a single run might not necessarily mean it was the same session either. Each batch could be executed in a different session that just happened to receive the same ID, similar to how different executions of SQLCMD were running in different sessions with the same ID.

Therefore, my question still stands: are multiple batches of the same script executed in SQLCMD in the same session or not? Is there a way to determine this?

Answer 1

You should be able to verify that all batches in a single SQLCMD script are run in the same session by creating a local #temp table at the top of the SQLCMD script and trying to reference that temp table in subsequent batches. Since the scope of a local temp table ends when the session ends, if subsequent batches were indeed running in different sessions (even though the id was the same), you would get table not found errors.

create table #test(TestColumn varchar(50))
insert into #test(TestColumn) values('I''m still here!')
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO

SELECT @@SPID
SELECT * FROM #test
GO