Temporary tables in stored procedures

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Nope  Independent instances of the temporary table will be created per each connection

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Temporary tables in stored procedures

Examples related to sql-server

Examples related to stored-procedures