CODEWITHSUNDEEP
sql-server
Byron Whitlock
Byron Whitlock

Reputation: 53871

Are temp tables in SQL Server multiuser safe?

Is a temp table local to the thread or global to the server?

Upvotes: 17

Views: 8618

Answers (4)

pjp
pjp

Reputation: 17639

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/

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294307

#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.

Upvotes: 28

Mehrdad Afshari
Mehrdad Afshari

Reputation: 422016

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.

Upvotes: 0

KM.
KM.

Reputation: 103597

createing a #tempTable is local in scope, but can be shared within nested called stored procedures

Upvotes: 0

Related Questions