Temporary Tables

Temporary tables are useful workspaces, like scratch pads, that you can use to try out intermediate data processing or to share work-in-progress with other connections. You can create temporary tables from within any database, but they exist in only the tempdb database, which is created every time the server is restarted. Don't assume that temporary tables aren't logged: temporary tables, and actions on those tables, are logged in tempdb so that transactions can be rolled back as necessary. However, the log isn't used for recovery of the database at system restart because the database is entirely re-created. Likewise, tempdb is never restored from a backup, so the log in tempdb is never needed for restoring the database. Unlike earlier versions of SQL Server, SQL Server 7 can log just enough information to allow rollback of transactions, without logging the additional information that would be necessary to recover those transactions, either at system startup or when recovering from a backup. This reduced logging means that data modification operations on tables in tempdb can be up to four times faster than the same operations in other databases. You can use temporary tables in three ways in SQL Server: privately, globally, and directly.

Private Temporary Tables (#)

By prefixing a table name with a single pound sign (#) ”for example, CREATE TABLE #my_table ”the table can be created from within any database as a private temporary table. Only the connection that created the table can access the table, making it truly private. Privileges can't be granted to another connection. As a temporary table, it exists for the life of that connection only; that connection can drop the table via DROP TABLE. Because the scoping of a private temporary table is specific to the connection that created it, you won't encounter a name collision should you choose a table name that's used in another connection. Private temporary tables are analogous to local variables ”each connection has its own private version, and private temporary tables that are held by other connections are irrelevant. (However, temporary tables do differ from local variables in one crucial way: temporary tables exist for the life of the session, while local variables exist only for a single batch.)

Global Temporary Tables (##)

By prefixing the table name with double pound signs (##) ”for example, CREATE TABLE ##our_table ”a global temporary table can be created from within any database and any connection. Any connection can subsequently access the table for retrieval or data modification, even without specific permission. Unlike private temporary tables, all connections can use the single copy of a global temporary table. Therefore, you can encounter a name collision if another connection has created a global temporary table of the same name, and the CREATE TABLE statement will fail.

A global temporary table exists until the creating connection terminates and all current use of the table completes. After the creating connection terminates, however, only those connections already accessing it are allowed to finish, and no further use of the table is allowed. If you want a global temporary table to exist permanently, you can create the table in a stored procedure that's marked to autostart whenever SQL Server is started. That procedure can be put to sleep using WAITFOR and it will never terminate, so the table will never be dropped. Or you can choose to use tempdb directly, which is discussed next .

Direct Use of tempdb

Realizing that tempdb is re-created every time SQL Server is started, you can use tempdb to create a table or you can fully qualify the table name to include the database name tempdb in the CREATE TABLE statement issued from another database. To do this, you need to establish create table privileges in tempdb . You can set up privileges in tempdb in one of two ways every time SQL Server starts: you can set the privileges in model (the template database) so that they are copied to tempdb when it's created at system restart, or you can have an autostart procedure set the tempdb privileges every time SQL Server is started. One reason to consider not setting the privileges for tempdb in the model database is because tempdb isn't the only database that will be affected. Any new database you create will inherit those permissions too.

Tables directly created in tempdb can exist even after the creating connection is terminated , and the creator can specifically grant and revoke access permissions to specific users:

 Creating a table in tempdb from pubs. Another method would be   to first do a 'use tempdb' instead of fully qualifying   the name. CREATE TABLE tempdb.dbo.testtemp (col1 int) 

Constraints on Temporary Tables

A few articles about SQL Server erroneously state that constraints don't work on temporary tables. However, all constraints work on temporary tables explicitly built in tempdb (not using the # or ## prefixes). All constraints except FOREIGN KEY constraints work with tables using the # (private) and ## (global) prefixes. FOREIGN KEY references on private and global temporary tables are designed not to be enforced, because such a reference could prevent the temporary table from being dropped at close-connection time (for private temporary tables) or when a table goes out of scope (for global temporary tables) if the referencing table wasn't dropped first.