• Repost - originally posted 14-06-2012

Local and Global Temporary Tables


Whilst reviewing some code recently I saw the creation of a global temporary table (##). There was actually no need to create a global temporary table - so it was changed to a local temporary table (#).

The following is a brief summary on using local and global temporary tables.

Local temporary are visible only to the connection that creates them and the tables are destroyed when you close your connection to SQL Server. There is no need to manually purge them under normal circumstances. If you maintain a persistent connection, or connection pooling, you may want to get in the habit of dropping temporary tables immediately after use.

Global temporary tables, on the other hand, since they are visible to all users in a given database, are destroyed along with the last connection which references them In spite of the fact that temporary tables are dropped when a connection terminates, it is often still considered good practice to explicitly delete such tables in order to be absolutely certain clean up has taken place.

You can always query tempdb to see what temporary objects are in use. SELECT * FROM tempdb..sysobjects where xtype = 'U' -- for user created tables


Recent Posts

See All

Migrating SSIS packages to AWS

We are currently migrating a large amount of SSIS packages (SQL Server 2008R2) to SQL Server 2016 at AWS. It has been a learning exercise to get these packages running. What we have learnt (so far) is