Contents
Can a temp table be created in three different sessions?
I created 3 temp tables called #TempTable in three different sessions in my SSMS, now if I go to the tempdb I can see the temp tables created there with a random (unique) string appended to each temp table’s name. Yes, multiple applications will get their own copies of the #temp table.
Is it possible to block a temp table in SQL?
You can get blocking if you create #temp tables inside a transaction. While this is generally not recommended I have seen this done a lot. However the blocking this causes is on some system tables in tempdb that do not affect other connections from creating temp tables (except perhaps for SQL versions prior to 2000?).
Can a temp table be created inside a transaction?
The statement would run instantly – creating your temp table and avoiding any possible locking. Then you could insert into it as usual: You can get blocking if you create #temp tables inside a transaction. While this is generally not recommended I have seen this done a lot.
What does it mean when SQL Server is blocking?
Blocking occurs when more than one session requests a lock on a resource, such as a row, page, or table, but SQL Server is not able to grant that lock due to another session already holding a non-compatible lock on that resource.
Can you create multiple copies of a temp table?
Yes, multiple applications will get their own copies of the #temp table. That is the point of using a #temp table, because each concurrent session has their own, isolated object.
Can a tempdb table be more than global?
The tables in tempdb can be more than just global and local temp tables. Therefore, you need to limit the result set with a where clause to list just global temp tables or local temp tables. The following script provides the syntax for listing just global temp tables or local temp tables.
What happens if I create a temporary table with the same name?
That means after you create a temporary table with the same name as some existing table, you can not access the normal table by that name, but only the temporary one (during that session).