Are temp tables in SQL Server multiuser safe?

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

  • Writing specific bytes in T-SQL binary datatype
  • Strange error occurring when using wcf to run query against sql server
  • How to self-join table in a way that every record is joined with the “previous” record?
  • Freeing up space in SQL Server
  • SQL Server Stored Procedure Update not working with ASP.Net C# GridView RowUpdating
  • Get records which are not in tables when passed in In clause?
  • 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
    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.