Real world scenario of using global temp table-CodePudding

Is there any real world scenario of using global temp table in SQL Server? I understand global temp table is shared by any active existing connection, which means it should be created by the first connection session, and will be deleted automatically when all connections are closed. I've searched the internet found a lot of information of what a global temp table is, and how to use it, however I can't find any real world use case.

I'm asking this question because I'm developing a .NET data access library and I'm wondering whether global temp table should be supported. Global temp table sounds a little bit weird to me because it must be created by the first connection, and since it will be deleted automatically when all connections closed, checking existence of global temp table is required when creating it. This may also introduce race condition. IMO a permanent table is simpler except the global temp table may have some performance advantage because it exists in the tempdb.

CodePudding user response：

I was on a project that once used global temp tables for a one-time data migration that involved running several generated SQL scripts in sequence in separate SSMS tabs. Those scripts used global temp tables to share data. When complete, closing the tabs (in particular the tab that created the temp tables) quietly cleaned up the working data.

Other than that, I've never seen them used.

CodePudding user response：

Short answer: There will always be a better solution than using global temporary tables.

The only time I've found value with a global temp table is when I'm trying to generate a table that can be accessed by anyone that's seated at a separate terminal than I am, and where I don't need to be concerned about cleanup.

I used to hold monthly learning seminars for my coworkers that weren't very technical (think BAs and QAs). To have them practice in real-time and have everyone see each other's inserts, deletes, and experience concurrency issues, I created global temporary tables on our servers so we didn't run the risk of executing on tables that held real data.