Sql-server – Can a temporary table be shared across sessions

sql servertemporary-tables

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?

Best Answer

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.

Related Solutions

Postgresql – What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table

In more recent versions of Postgres (I think since 8.3) you can assign a special tablespace for temporary tables which might help you. This is documented here:

http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-TEMP-TABLESPACES

Given the fact that 8.2 will be de-supported at the end of the year it might be a good idea to upgrade. There have been numerous enhancements to VACUUM and the handling of temporary files since 8.2 so you might benefit from those.

Edit:
The reason why I think this (separate tablespace) could help you, is that you can simply drop and recreate the tablespace (files) to reclaim the spaces occupied.

But then I'd assume that due to all the improvements that were implemented in the last 5 years the current version might release the space without any further action from your side (especially because VACUUM FULL has been completely rewritten in 9.0)

Sql-server – Table Variable and Database Scope

Table variables have the scope of the batch or stored procedure they are run in. There is no need to drop the table variable at the end of a batch, however you will not be able to reference the table variable in consecutive batches.

If you execute the SQL as one block, without the exec, then your SELECT will be able to 'see' the table variable and select the data from it.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

select col1 from @T1; -- returns 10

go

The exec encapsulates a T-SQL batch, so in your example it is in a completely separate batch from the table declaration and the insert statement and therefore the table variable is not in scope.

Exec does not terminate a batch though, the table variable will still be in scope after the exec is run.

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

select col1 from @T1; -- returns 10

go

However, the GO command signals the end of a batch, so placing a GO between the insert and select will also mean you are executing 2 separate batches. You would then also get the error

declare @T1 as table 
(   
    col1 int not null
);

insert into @T1(col1) values (10);

exec('select col1 from @T1;'); -- errors

GO

select col1 from @T1; -- errors

GO

Best Answer

Related Solutions

Postgresql – What are all the reasons/possibilities that may fail to release disk space occupied by TEMPORARY table

Sql-server – Table Variable and Database Scope

Related Question