Local and global temporary tables in SQL Server

sql-server temp-tables global-temp-tables
andrew Sullivan picture andrew Sullivan · May 27, 2010 · Viewed 282.1k times · Source

What is the difference between local and global temporary tables in SQL Server?

Answer

Anthony Faull picture Anthony Faull · May 27, 2010

  • Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

  • Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.

  • Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.

  • Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

Related questions

How to see temp table created by code in sql server?

I create a global temp table (i.e ##TheTable) using C# code. I want to be able to see that temp table in SQL server management studio after the code runs completely. Is it possible to do this ? If yes, …

Read More
Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later, …

Read More
Inserting data into a temporary table

After having created a temporary table and declaring the data types like so; CREATE TABLE #TempTable( ID int, Date datetime, Name char(20)) How do I then insert the relevant data which is already held on a physical table within the …

Read More