In
this post I will describe the differences between a Local temporary table and
global temporary table.
Below are the differences in tabular form:
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