Temporary Tables

Temporary tables are useful as work tables to hold data while processing, such as building a table using derived data that will be referenced for further calculations.

Creating Temporary Tables

Tables are designated as temporary in SQL Server by prefacing the table name with a number sign (#). Temporary tables are created in tempdb ; if they are not explicitly dropped, they are dropped when the session that created them ends. If a table is prefaced with a single pound sign (#table1), it is a private temporary table, available only to the session that created it. A table prefixed by a double pound sign (##table2) indicates that it is a global temporary table, which means it is accessible by all database connections. Global temporary tables exist until the session that created them terminates. If the creating session terminates while other sessions are accessing the table, the temporary table is available to those sessions until the last session's query ends, at which time the table is dropped. Tables created without the # prefix but explicitly created in Tempdb are also considered temporary, but they are a more permanent form of a temporary table. They will not be dropped automatically until SQL Server is restarted and tempdb is reinitialized.