Global temp tables are created by following query.
CREATE TABLE [dbo].[##Temp] (ID INT)
if global temp tables are created and used
by single instance only, problem(creating 2 temp table with same name) will not occur. The problem will occur when another instance creating same temp table at same time. This problem will occur on same database or another database with same name of temp table creation.
To avoid this confliction, we will use GUID with temp table name. But this method is more constlier and difficult to handle. Because temp table name is so lengthy.
EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, NEWID()) + ' ] (ID INT)' )
Otherwise we can use identity value of some table with temp table name.
EXEC ( 'CREATE TABLE [dbo].[##Temp' + @ID + ' ] (ID INT)' )
The above method will solve the problem even multiple instances running on same database. Because each instance will create unique identity value and using that id with temp table name.
Again problem will occur between different databases creating the same temp table with same identity value. This problem will occur when the multiple instance running on different databases at the same time. Because each instance trying to create temp table with same name.
To avoid confliction between different databases, use DB_ID() or DB_NAME() with temp table name.
EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, DB_ID()) + @ID + ' ] (ID INT)' )
EXEC ( 'CREATE TABLE [dbo].[##Temp' + CONVERT(VARCHAR, DB_NAME()) + @ID + ' ] (ID INT)' )
Because DB_ID() or DB_NAME() will be unique to each database.
