How Does SQL Server Ensure Isolation of Temporary Tables in Concurrent Processes?

Scope of Temporary Tables in SQL Server

When working with SQL Server, it's crucial to understand the scope of temporary tables to prevent data corruption. This article explores the misconceptions surrounding temporary table sharing in multi-user environments and clarifies the role of EXEC in creating new scopes.

Temporary Table Scope

Contrary to the claim that temporary tables are shared, SQL Server ensures isolation for concurrent import operations. As stated in Microsoft's documentation:

• Local temporary tables are visible only within the current session.
• The database engine appends a numeric suffix to temporary table names created in concurrent sessions, distinguishing them.

Scope of EXEC

Each call to EXEC creates a new scope. This means that a temporary table created within a stored procedure is not visible to other stored procedures executing concurrently.

Consider the example provided:

CREATE PROC [dbo].[ImportCompany]
(
    @CompanyId AS INTEGER
)
AS
EXEC [dbo].[ImportAddress] @CompanyId = @CompanyId 
--Import other data

CREATE PROC [dbo].[ImportAddress]
(
    @CompanyId AS INTEGER
)
AS
    CREATE TABLE #Companies (OldAddress NVARCHAR(128), NewAddress NVARCHAR(128))
    INSERT INTO #Companies(OldAddress, NewAddress)
    SELECT
        Address as OldAddress,
        'Transformed ' + Address as NewAddress
    FROM
        [OldDb].[dbo].[Addresses]
    WHERE
        CompanyId = @CompanyId

    --Do stuff with the transformed data

    DROP TABLE #Companies

EXEC [dbo].[ImportCompany] @CompanyId = 12345

In this case, the #Companies table is automatically dropped at the end of the ImportAddress procedure, ensuring that no concurrent import operation can access or corrupt it.

Conclusion

Temporary tables in SQL Server are scoped to the current session and cannot be shared between concurrent imports. Each call to EXEC creates a new scope, ensuring isolation and preventing data corruption. Therefore, the use of table variables is not necessary for maintaining data integrity in this scenario.

The above is the detailed content of How Does SQL Server Ensure Isolation of Temporary Tables in Concurrent Processes?. For more information, please follow other related articles on the PHP Chinese website!