• 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

3 views0 comments

Recent Posts

See All

Standardise database sizes and automation

The default setting for the initial sizing of data and log files (and their autogrowth) is not adequate. I like to make sure that we set standard sizes for our data and log files - plus standard sizes

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

Autoclose and autoshrink - set them to off

By running a simple check of your database settings (use helpdb or query sys.databases) you may find databases that are set to autoclose and/or autoshrink. If so, you should change these settings as p