Using SS 2000. BOL says that local temporary tables are private to the
"current session". I assume this means to the connection. For example,
If my app is connected to SQL Server and runs a process that creates a
temp table and I then run a second instance of my app on the same PC
and log in as the same user and run the same process I assume that each
instance of my app will have its own temp table. Is this correct?

On the other hand, if my app attempts to run the process that creates
the local temp table twice at the same time in two different threads
there will be a collision because both threads will see the same
instance of the temp table. Is this correct?

Thanks.

Under both circumstances you would see two separate temp tables.  SQL has no
clue that two different connections belong to the same application.
Application threading models don't matter to SQL since all it sees are
connections.

From the updated BOL:
Temporary tables are automatically dropped when they go out of scope, unless
explicitly dropped using DROP TABLE:

  a.. A local temporary table created in a stored procedure is dropped
automatically when the stored procedure completes. 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 which
called the stored procedure that created the table.


  b.. All other local temporary tables are dropped automatically at the end
of the current session.
----- end excerpt

Most of the time "session" is the same as "connection" for this purpose.
One exception is when you use an EXECUTE command to run dynamic SQL.
Everything inside that command is a separate session, just like temp table
scoping within a stored procedure.

Show quoteHide quote

Bill wrote:

Show quoteHide quoteThe 2 different threads will most likely use separate connections to the
SQL server, in which case the temp tables will be created in different
sessions and so get different names in tempdb (as the session context is
included (invisibly...sort of) in the table name in tempdb).  However,
if the 2 threads manage to share a connection to the SQL server, for
example in a connection pooling scenario, then if you give the temp
tables the same name in both threads the SQL server will return an error
message to the second thread (that attempted to create the table) to the
affect that the table already exists.

Mike Hodgson (e1mins***@gmail.com) writes:
Note that in normal connection pooling this would not happen. Each
thread would get a connection, which may be from the same pool, but
they would not get the same connection at the same time.

But if you declare your connection object on global level in your
app, and start to run multiple threads, that uses this connection
(which would be protected by a semaphore) this could happen. But
I don't think very many people would consider such design.

Returning complex calculation as column?
2-nd edition of Celko Puzzle book in the works
Re: Store multi-choice groupbox selection in a field
PERFORMANCE QUESTION
creating sp to export column to comma delimited list
The IDENTITY function can only be used when the SELECT statement has an INTO clause
Is there an easier way to do multiple aggregate columns in tsql
Recursive Query/CTE - SQL 2K5
table check constraint function
CLR UDT problem: Data type "0xF0" (user-defined type) has an invalid user type specified