Cogslave Cogslave - 1 month ago 7
SQL Question

Scope of temporary tables in SQL Server

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 the script review I was told to use table variables rather than temporary tables.
This is because if we ran two different imports at the same time, the temporary table would be shared and corrupt the import.




Questions:


  • Is this true?

  • I thought each call to
    EXEC
    created 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

Answer

From CREATE TABLE:

Local temporary tables are visible only in the current session

and (more importantly):

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. 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.


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