If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp table created in the session that executes sp_ExecuteSql.

In other words, why does this work:

SELECT 1 AS TestColumn
INTO #TestTempTable

DECLARE @DS NVARCHAR(MAX) = 'SELECT * FROM #TestTempTable'
EXEC sp_EXECUTESQL @DS

Results:

My understanding for the reason why I can't do the opposite (create the temp table in Dynamic SQL and then access it outside the dynamic SQL query in the executing session) is because sp_ExecuteSql executes under a new session.

From an answer by Remus Rusanu:


Dynamic SQL runs in the same session as the calling code. The issue is
not session, but scope. Your dynamic SQL creates the temporary tables
inside the sp_executesql call and thus the created #temp table is only
visible within that sp_executesql call, as documented in MSDN:

You can validate that you are using the same session in the sp_executesql call by running:

DECLARE @DS NVARCHAR(MAX) = 'SELECT @@SPID'
EXEC sp_EXECUTESQL @DS

Knowing that sp_executesql runs under a different scope but not a different session, adding information found in the docs on temporary tables:


Temporary tables are automatically dropped when they go out of scope,
unless explicitly dropped by using DROP TABLE:

And that same source on the visibility of temporary tables:


Local temporary tables are visible only in the current session

These differences in scope vs. session explain why you are able to access the temporary table from the sp_executesql call but not the other way around.

StackExchange Database Administrators Q#255202, answer score: 11