Tip: How to create a global temporary table in SQL Server

The life cycle of the global temporary table persists until the creation session (not the creation level) terminates. For example, if you create a global temporary table in a stored procedure, the table is not destroyed when the scope of the stored procedure is exceeded. When the creation session terminates, SQL Server automatically attempts to delete the table, and all statements submitted to it in other sessions will end and release all the locks they hold.

But in some cases, you might want to create a global temporary table that does not belong to any session. At this point, no matter which session is turned on or off, the total number exists and can only be removed if it is explicitly deleted. To do this, you can create the table in a special stored procedure (created in master using the SP_ prefix) and mark the stored procedure with the "Startup" option. SQL Server invokes the startup process (startup procedure) at each startup. Also, SQL Server maintains a reference counter that is greater than 0 for the global temporary table created during startup, ensuring that SQL Server does not attempt to delete it automatically.

As shown below:

USE　master;
GO
IF　OBJECT_ID('dbo.sp_Globals')　IS　NOT　NULL
 　DROP　PROC　dbo.sp_Globals
GO
CREATE　proc　dbo.sp_Globals
AS
 
CREATE　TABLE　##Globals
(
id　varchar(36)　NOT　NULL　PRIMARY　KEY,
value　varchar(500)
);
GO
 

EXEC dbo.sp_procoption ' dbo.sp_globals ', ' Startup ', ' true ';

Global temp Table # #Globals会自动重建并一直持续到被显式删除 after execution and restart of SQL Server.