Difference Between a Local and a Global Temporary Table

Question

Hi What is the difference between a Local and a Global Temporary Table ?

Akkiraju Ivaturi · Answer

According to sql magazine: 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. 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.

Satyapriya Nayak · Answer

http://sql-plsql.blogspot.in/2011/06/local-global-temporary-table.html

Ish Bandhu · Answer

Local Temp Local temp table is defined by prefix # and the scope of local temp, the session in which it is created. Global Temp: Global temp table is defined by prefix ## and the scope of global temp can be seen by all sessions connected to the server