In SQL Server 2008 ,is it possible to disable auto drop of global temp table

Question

Hi

question1 :

I am using global temp tables in SQL Server 2008

but once my connection is closed this temp is dropped

is there any way to disable auto drop

Question2 :

If two connections are accesstion same global temp table and another connection is trying to delete that global temp table, does SQL Server handles this synchronization properly?

from nayeem khan

Answer 1

If you need a table to persist beyond the death of the connection that created it, you should just create a regular table instead of a temporary one. It can still be created in tempdb directly (geting you the benfits of simple logging and auto destruction on server restart) but it's name wouldn't be prefixed by ##.

DROP TABLE is a transactional statement that will block if there are any active connections using that table (with locks).

Answer 2

When the connection that created the ##GlobalTempTable ends, the table will be dropped, unless there is a lock against it.

You could run something like this from the other process to keep the table from being dropped:

BEGIN TRANSACTION
    SELECT TOP 1 FROM ##GlobalTempTable WITH (UPDLOCK, HOLDLOCK)


...COMMIT/ROLLBACK

However, when the transaction ends, the table will be dropped. If you can't use a transaction like this, then you should use a permanent table using the Process-Keyed Table method.

Answer 3

You can create your global temp tables in a stored procedure and mark it with the startup option.

SQL Server maintains a reference count greater than zero for all global temporary tables created within startup procedures.

some example code

CREATE PROC dbo.CreateGlobalTempTables
AS
CREATE TABLE ##my_temp_table
(
   fld1 INT NOT NULL PRIMARY KEY,
   fld2 INT NULL
);
GO

EXEC dbo.sp_procoption 'dbo.CreateGlobalTempTables', 'startup', 'true';

The global temporary table will be created automatically at startup and persist until someone explicitly drops it.