Menu
Is a temp table local to the thread or global to the server?
805
Emulating Oracle Package Variables using Temporary TablesPackage variables are not shared between Oracle sessions (connections), each session has its own copy of data in the variables.
If you will have a very large result set, or need to refer to it more than once, put it in a #temp table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE is preferred.
Local temporary objects (Tables and Stored Procedures) are completely safe from being seen by other sessions.
Global temp tables can be referenced from the same connection or a different connection so long as the global temp table has not gone out of scope. The database connection is the same when a temp table is created and referenced from a script within the same SSMS tab.
#temp is session scope##temp is server scope
MSDN:
Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions
...
A local temporary table created in a stored procedure is dropped automatically when the stored procedure is finished. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process that called the stored procedure that created the table.
All other local temporary tables are dropped automatically at the end of the current session.
Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.
157
Local temp tables can be created using hash (#) sign prior to table name. They are visible only in current connection.. When connection is dropped its scope ends as well. It is possible to create and use local temp table with the same name simultaneously in two different connections. In order to allow this behavior SQL Server suffixes name of the local temp table with incremental hex digit, which is reset when SQL Services are restarted
see http://blog.sqlauthority.com/2009/03/29/sql-server-fix-error-msg-2714-level-16-state-6-there-is-already-an-object-named-temp-in-the-database/
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
