Clash Royale CLAN TAG#URR8PPP

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

Questions:

EXEC

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


4 Answers
4

From CREATE TABLE:

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):

DROP TABLE

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

the sentence 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 is confusing! Seems that the same user in different sessions shares temporary table. And is not like that.
– Alex
Oct 20 '15 at 10:51

@Alex - I agree that it could be somewhat confusing, but unfortunately, that's how Microsoft chose to phrase it.
– Damien_The_Unbeliever
Oct 20 '15 at 10:57

## is used for global temporary tables - will be available to the different imports.

##

# is used for local temporary tables and only available in the current/inner scope.

#

Local temporary tables are also visible in inner scopes, for example: sqlfiddle.com/#!6/d41d8/6725/1
– Andomar
Sep 4 '13 at 12:56

One session cannot see another session's temporary tables. So different imports will not affect each other, regardless of whether you use temporary tables or table variables.

The exception is global temporary tables, which start with ##. Those are visible to all connections.

##

I just spent a few hours struggling to find out why a temporary table used in a trigger behaved strangely. Then I realised that the temporary table had the same name as a temporary table in the stored procedure used to insert the data that fired the trigger.
I am now aware that this should have been obvious to me straight away, but it was a typical case of overlooking the most obvious cause when trying to find out why something did not make sense.

So it is important to remember that when a stored proc calls another stored proc or fires a trigger, then temp table names have to be unique across these in order to prevent undesired side-effects.

Furthermore - even when executing the following code in the inner stored proc, it will not function as expected. Since the outer stored proc seems to lock the temp table name.

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable


I feel like this answer is probably helpful and related to the question, but it might be better-received if you can change the wording to make it more clear how it directly impacts the situation that OP is asking about.
– DaveyDaveDave
May 25 at 10:45

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.