SQL Server 中临时表的范围 [英] Scope of temporary tables in SQL Server

问题描述

我编写了一个存储过程来将数据从一个数据库导入并转换到另一个数据库.每次导入都需要一个公司 ID 并导入与该公司相关的所有数据.

I wrote a stored procedure to import and transform data from one database to another. Each import would take a single company ID and import all data related to this company.

为了帮助完成转换步骤，我使用了临时表.作为脚本审查的一部分，我被告知使用表变量而不是临时表.评论者声称如果我们同时运行两个不同的导入，临时表将被共享并破坏导入.

To help with the transformation step I use temporary tables. As part of script review, I was told to use table variables rather than temporary tables. The reviewer claims that if we run two different imports at the same time, the temporary table would be shared and corrupt the import.

问题:

• 如果我们同时运行两个不同的导入，临时表会被共享吗?
• 每次调用 EXEC 都会创建一个新的作用域吗?

• Is it true that the temporary table would be shared if we run two different imports at the same time?
• Does each call to EXEC create a new scope?

这是一个人为的脚本示例.

Here is a contrived example of the script.

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

推荐答案

来自 <代码>创建表:

本地临时表只在当前会话中可见

Local temporary tables are visible only in the current session

和(更重要的):

如果在一个存储过程或应用程序中创建了一个可以被多个用户同时执行的本地临时表，那么数据库引擎必须能够区分不同用户创建的表[原文如此 - 几乎当然，这应该说会话而不是用户].数据库引擎通过在内部为每个本地临时表名称附加一个数字后缀来实现这一点.

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users [sic - almost certainly this should say sessions not users]. The Database Engine does this by internally appending a numeric suffix to each local temporary table name.

这恰恰反驳了谁说他们会被分享的观点.

Which exactly rebuts the point of whoever said that they would be shared.

此外，无需在程序结束时DROP TABLE(再次来自同一个链接):

Also, there's no need to DROP TABLE at the end of your procedure (from same link again):

在存储过程中创建的本地临时表在存储过程完成时自动删除

A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished

这篇关于SQL Server 中临时表的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！