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