Local temporary table VS Global temporary table

In this post I will describe the differences between a Local temporary table and global temporary table.
Below are the differences in tabular form:
 
Local Temporary Table
Permanent Temporary Table
They have a single number sign (#) as the first character of their names.
They have a two number sign (##) as the first character of their names.
They are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server.
They are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
If local temp table is created inside a stored procedure then it will get deleted once the execution is completed.
If global temp table is created inside a stored procedure then it will exists once the execution is completed. This will dropped automatically when the last user session that references the table disconnects.
Multiple users can't share these tables.
Multiple users can share these tables; in fact, all other database users can access it.
You can't grant or revoke permissions on these tables because it is available only to the owner.
You can grant or revoke permissions on these tables because these are always available to everyone.
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.
These tables will always available till the last user session that references the table disconnects.

No comments:

Post a Comment