[sql-server] Temporary tables in stored procedures

I have been wondering about temp tables in sp's and how all that can effect concurrency. SP made on a MSSQL 08 server.

If I have a SP where I create a temp table and drop it again like this:

BEGIN

CREATE TABLE #MyTempTable
(
   someField int,
   someFieldMore nvarchar(50)
)

... Use of temp table here
... And then..

DROP TABLE #MyTempTable

END

This SP will be called very very often, so my question is can there ever occur concurrency issues here?

According SQL Server 2008 Books You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions.

'#table_temporal

'##table_global

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.

Then there occurs no problem.

Maybe.

Temporary tables prefixed with one # (#example) are kept on a per session basis. So if your code calls the stored procedure again while another call is running (for example background threads) then the create call will fail because it's already there.

If you're really worried use a table variable instead

DECLARE @MyTempTable TABLE 
(
   someField int,
   someFieldMore nvarchar(50)
)

This will be specific to the "instance" of that stored procedure call.

Use @temp tables whenever possible--that is, you only need one primary key and you do not need to access the data from a subordinate stored proc.

Use #temp tables if you need to access the data from a subordinate stored proc (it is an evil global variable to the stored proc call chain) and you have no other clean way to pass the data between stored procs. Also use it if you need a secondary index (although, really ask yourself if it is a #temp table if you need more than one index)

If you do this, always declare your #temp table at the top of the function. SQL will force a recompile of your stored proc when it sees the create table statement....so if you have the #temp table declaration in the middle of the stored proc, you stored proc must stop processing and recompile.

The database uses the same lock for all #temp tables so if you are using a lot you will get deadlock problems. It is better to use @ table variables for concurrency.

For short: No

Explanation:

According to the official docs: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15

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. The full name of a temporary table as stored in the sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

For all those recommending using table variables, be cautious in doing so. Table variable cannot be indexed whereas a temp table can be. A table variable is best when working with small amounts of data but if you are working on larger sets of data (e.g. 50k records) a temp table will be much faster than a table variable.

Also keep in mind that you can't rely on a try/catch to force a cleanup within the stored procedure. certain types of failures cannot be caught within a try/catch (e.g. compile failures due to delayed name resolution) if you want to be really certain you may need to create a wrapper stored procedure that can do a try/catch of the worker stored procedure and do the cleanup there.

e.g. create proc worker AS BEGIN -- do something here END

create proc wrapper AS
BEGIN
    Create table #...
    BEGIN TRY
       exec worker
       exec worker2 -- using same temp table
       -- etc
    END TRY
    END CATCH
       -- handle transaction cleanup here
       drop table #...
    END CATCH 
END

One place where table variables are always useful is they do not get rolled back when a transaction is rolled back. This can be useful for capturing debug data that you want to commit outside the primary transaction.

Not really and I am talking about SQL Server. The temp table (with single #) exists and is visible within the scope it is created (scope-bound). Each time you call your stored procedure it creates a new scope and therefore that temp table exists only in that scope. I believe the temp tables are also visible to stored procedures and udfs that're called within that scope as well. If you however use double pound (##) then they become global within your session and therefore visible to other executing processes as part of the session that the temp table is created in and you will have to think if the possibility of temp table being accessed concurrently is desirable or not.