48

I'm using SQL Server 2000, and many of the stored procedures it use temp tables extensively. The database has a lot of traffic, and I'm concerned about the thread-safety of creating and dropping temp tables.

Lets say I have a stored procedure which creates a few temp tables, it may even join temp tables to other temp tables, etc. And lets also say that two users execute the stored procedure at the same time.

  • Is it possible for one user to run the sp and which creates a temp table called #temp, and the another user runs the same sp but gets stopped because a table called #temp already exists in the database?

  • How about if the same user executes the same stored procedure twice on the same connection?

  • Are there any other weird scenarios that might cause two users queries to interfere with one another?

Juliet
  • 76,873
  • 44
  • 191
  • 224

9 Answers9

38

For the first case, no, it is not possible, because #temp is a local temporary table, and therefore not visible to other connections (it's assumed that your users are using separate database connections). The temp table name is aliased to a random name that is generated and you reference that when you reference your local temp table.

In your case, since you are creating a local temp table in a stored procedure, that temp table will be dropped when the scope of the procedure is exited (see the "remarks section").

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

For the second case, yes, you will get this error, because the table already exists, and the table lasts for as long as the connection does. If this is the case, then I recommend you check for the existence of the table before you try to create it.

ruffin
  • 13,513
  • 8
  • 72
  • 118
casperOne
  • 70,959
  • 17
  • 175
  • 239
  • how can you execute the same proc in parallel from one connection? – SQLMenace Jan 21 '09 at 21:03
  • 1
    You can execute the same proc twice on the same connection (not in parallel) and not dropping the temp table after the first time you run the sproc. In that case, the temp table is still there. – Jeroen Landheer Jan 22 '09 at 17:13
  • 1
    Sorry, @CasperOne, but your information is not accurate: temp tables created inside a stored procedure are automatically dropped when the SP finishes. There's no problem on calling such SPs twice on the same connection. – Gerardo Lima May 08 '12 at 18:22
  • @GerardoLima Updated the answer to reflect your comment with a reference to the documentation. – casperOne May 08 '12 at 18:37
9

Local-scope temp tables (with a single #) are created with an identifier at the end of them that makes them unique; multiple callers (even with the same login) should never overlap.

(Try it: create the same temp table from two connections and same login. Then query tempdb.dbo.sysobjects to see the actual tables created...)

Joe
  • 38,368
  • 16
  • 103
  • 119
5

Local temp tables are thread-safe, because they only exist within the current context. Please don't confuse context with current connection (from MSDN: "A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished"), the same connection can safely call two or more times a stored procedure that creates a local temp table (like #TMP).

You can test this behavior by executing the following stored procedure from two connections. This SP will wait 30 seconds so we can be sure the two threads will be running their over their own versions of the #TMP table at the same time:

CREATE PROCEDURE myProc(@n INT)
AS BEGIN
    RAISERROR('running with (%d)', 0, 1, @n);
    CREATE TABLE #TMP(n INT);
    INSERT #TMP VALUES(@n);
    INSERT #TMP VALUES(@n * 10);
    INSERT #TMP VALUES(@n * 100);
    WAITFOR DELAY '00:00:30';
    SELECT * FROM #TMP;
END;
Gerardo Lima
  • 5,786
  • 3
  • 27
  • 46
  • The examples you gave doesn't seem to prove your point: local temp tables `only exist within the current context.` `Please don't confuse context with current connection`. If it works as you described, a procedure should be called repeatedly without problem within the same session, as long as the local temp table in question is created inside the procedure's context, not out side of this procedure ? – Ben Jul 09 '18 at 09:35
  • Thanks, the temporary-tables section of the article you linked explained everything for me: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017#temporary-tables – Ben Jul 09 '18 at 10:03
5

The short answer is:

Isolation of temporary tables is guaranteed per query, and there's nothing to worry about either in regard to threading, locks, or concurrent access.

I'm not sure why answers here talk about a significance of 'connections' and threads as these are programming concepts, whereas query isolation is handled at the database level.

Local temporary objects are separated by Session in SQL server. If you have two queries running concurrently, then they are two completely separate sessions and won't intefere with one another. The Login doesn't matter, so for example if you are using a single connection string using ADO.NET (meaning that multiple concurrent queries will use the same SQL server 'login'), your queries will all still run in separate sessions. Connection Pooling also doesn't matter. Local temporary objects (Tables and Stored Procedures) are completely safe from being seen by other sessions.

To clarify how this works; while your code has a single, common name for the local temporary objects, SQL Server appends a unique string to each object per each session to keep them separate. You can see this by running the following in SSMS:

CREATE TABLE #T (Col1 INT)

SELECT * FROM tempdb.sys.tables WHERE [name] LIKE N'#T%';

You will see something like the following for the name:

T_______________00000000001F

Then, without closing that query tab, open up a new query tab and paste in that same query and run it again. You should now see something like the following:

T_______________00000000001F

T_______________000000000020

So, each time your code references #T, SQL Server will translate it to the proper name based on the session. The separation is all handled auto-magically.

Community
  • 1
  • 1
Chris Halcrow
  • 21,541
  • 11
  • 115
  • 145
3

Temp tables are tied to the session, so if different users run your procedure simultaneously there's no conflict...

Jason Punyon
  • 37,168
  • 13
  • 93
  • 118
2

Temp tables are created only in the context of the query or proc that creates them. Each new query gets a context on the database that is free of other queries' temp tables. As such, name collision is not a problem.

Yes - that Jake.
  • 15,710
  • 14
  • 68
  • 94
  • I think you can say that for each procedure, but not each query. Or one will not be able to query the same temp table later. unless you mean the query `window` ? – Ben Jul 09 '18 at 10:01
1

If you look in the temps database you can see the temporary tables there, and they have system generated names. So other than regular deadlocks you should be OK.

Otávio Décio
  • 70,467
  • 17
  • 156
  • 220
0

unless you use two pound signs ##temp the temp table will be local and only exists for that local connection to the user

SQLMenace
  • 125,031
  • 23
  • 195
  • 219
0

First let's make sure you are using real temp tables, do they start with # or ##? If you are creating actual tables on the fly and then dropping and recreating them repeatedly, you will indeed have problems with concurrent users. If you are createing global temp tables (ones that start with ##) you can also have issues. If you do not want concurrency issues use local temp tables (They start with #). It is also a good practice to explicitly close them at the end of the proc (or when they are no longer needed by the proc if you are talking long multi-step procs) and to check for existence (and drop if so) before creating.

HLGEM
  • 88,902
  • 11
  • 105
  • 171