Can a temporary table be shared across sessions?

Question

I have revised a procedure that builds a dynamic SQL statement to create and populate a temporary table. The syntax was something like this:

...'create #temp_' + CAST(GETGUID() AS VARCHAR(36)) ...

I asked a colleague if he knew why a unique identifier was being concatenated to the table name. He told me that in the past there were cases where temporary tables were shared across sessions and the data was messed up.

I know this is not possible since MSDN clearly states:

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions.

Now (remembering the case) some colleagues think we should use the syntax above as a good practice but I do not like it and need proof there is no such bug or if it was it is already fixed.

Could anyone with experience in SQL Server 2005/2008 tell me if there ever was such case?

Answer 1

Local table tables should never be shared across sessions. As you've pointed out, they are only visible to the session that created them and are destroyed when the creating session is terminated, or you drop them.

Global temporary variables are visible to all sessions, but you'd need to define them with the double hash i.e. ##temp, for them to be defined as global.

As for putting a suffix on the table name when creating it, you're wasting your time, as SQL Server does that anyway.

CREATE TABLE #temp (Name VARCHAR(20));

USE tempdb;
GO

SELECT name FROM sys.tables WHERE name LIKE '#temp%';

For me, this returns

#temp_______________________________________________________________________________________________________________00000000004C

However, as Aaron Bertrand pointed out in this answer:

if you call everything #temp, or #t, or #x, then it's possible that such a table already exists from an outer scope prior to calling the procedure.

In this case, you could be sharing the temp table between a top-level process and a number of different procedures that it calls within that process. This of course could lead to data corruption, depending on how you defined and constructed your temporary table.

Answer 2

Local temporary tables should not have this problem, but if you want to ensure that the table creation doesn't cause a DDL conflict then just put a conditional drop statement at the top of the statement, see below.

if ( object_id('tempdb..#t') is not null )
begin
     drop table #t
end