Temporary Tables

There are two types of temporary tables: local and global. These temporary tables are created in tempdb and not within the database you are connected to. They also have a finite lifetime. Unlike a variable, the time such a table can “survive” is different.

1. A local temporary table survives until the connection it was created within is dropped. This can happen when the stored procedure that created the temporary table completes, or when the Query Editor window is closed. A local temporary table is defined by prefixing the table name by a single hash mark: #. The scope of a local temporary table is the connection that created it only.
2. A global temporary table is defined by prefixing the table name by a double hash mark: ##. The scope of a global temporary table differs significantly. When a connection creates the table, it is then available to be used by any user and any connection, just like a permanent table. A global temporary table will then be “deleted” only when all connections to it have been closed.