Are temp tables in SQL Server multiuser safe?
Is a temp table local to the thread or global to the server?
4 Solutions collect form web for “Are temp tables in SQL Server multiuser safe?”
#temp is session scope
##temp is server scope
Local temporary tables are visible
only in the current session, and
global temporary tables are visible to
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.
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
createing a #tempTable is local in scope, but can be shared within nested called stored procedures
Depends on what you mean by “temp table.” If you mean tables with
# prefix, their real name would contain the connection ID and is unique per connection. However, if you are manually creating a table in
tempdb database, it’ll be treated like all normal tables.