Temporary Tables: Local vs. Global

A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can't share a local temporary table because it is local to one user session. You also can't grant or revoke permissions on the local temporary table. (See also, "Table Variables vs. Temporary Tables").

A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. When the last user session that references the table disconnects, the global temporary table is lost. However, multiple users can access a global temporary table; in fact, all other database users can access it. But you can't grant or revoke permissions on the global temporary table because it's always available to everyone.

Local and global temporary tables differ in a subtle way. Let's look at what SQL Server Books Online (BOL) says about temporary tables. "Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use. The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes." The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures. Within its execution scope, an sp_executesql system stored procedure creates and drops a local table. By definition, when the sp_executesql procedure ends, the life of the table also ends—hence the need to create a global temporary table.

Learn more from "T-SQL Temporary Table Tips."