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. 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 (#)—as in CREATE TABLE #my_table—you can create it (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 using 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 a table name with double pound signs (##)—as in CREATE TABLE ##our_table—you can create a global temporary table (from within any database and any connection). Any connection can subsequently access the table for retrieval or data modification, even without specific permission. Unlike with private temporary tables, all connections can use the single copy of a global temporary table. Therefore, you might 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 I'll discuss 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 as well.

Tables created directly 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)