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

Question 1: I am using a 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

Question 2: If two connections are accessing same global temp table and another connection is trying to delete that global temp table, does SQL Server handles 开发者_如何学Cthis synchronization properly?

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.

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).

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.