Are SQL Server Temporary Tables Truly Shared During Concurrent Stored Procedure Executions?

Temporary Table Scope in SQL Server

In the context of stored procedures that import and transform data from one database to another, the use of temporary tables has been questioned in favor of table variables. The concern raised is the potential for data corruption if multiple imports are run concurrently, leading to shared temporary tables.

Scope of Temporary Tables

Unlike table variables, temporary tables are not scoped to the statement or batch in which they are created. Instead, temporary tables are scoped to the session in which they are created. SQL Server ensures that temporary tables created in different sessions are distinct and tamper-proof. This is achieved by appending a numeric suffix to the name of each temporary table.

Answer to Questions

1. Is it true that the temporary tables would be shared if two different imports are run at the same time?

   • No, temporary tables are scoped to the session and distinct for each execution of the stored procedure, even if multiple imports are running concurrently.

2. Does each call to EXEC create a new scope?

   • Yes, each invocation of a stored procedure using EXEC creates a new execution scope, including the creation of distinct temporary tables.

Dropping Temporary Tables

It is worth noting that temporary tables are automatically dropped at the end of the procedure. This is due to the session-level scope of temporary tables and eliminates the need for explicit DROP TABLE statements.

The above is the detailed content of Are SQL Server Temporary Tables Truly Shared During Concurrent Stored Procedure Executions?. For more information, please follow other related articles on the PHP Chinese website!