I was doing some consulting recently and reviewing a developer's code. I was surprised to find extensive use of global temporary tables. Let me start by saying this: global temporary tables are almost never what you should be using.
When I asked why he'd used them, he told me that he wanted the temporary tables that he created in his procedure, to be available to other procedures that were run from within the procedure. So in PROC-A, he was running PROC-B and PROC-C. A temporary table was created in PROC-A and he wanted to be able to use it in PROC-B and PROC-C.
Local vs Global Temporary Tables
Local temporary tables (i.e. tables with names starting with a single #) are available where they are created, and in any other procedures run from within that same scope. You don't need a global temporary table to do that.
Local temporary tables are dropped when they go out of scope (i.e. the session or context where they were created is dropped). And of course you can manually drop them with a DROP statement as well.
Ideally, you'd always drop them in your code rather than waiting for them to go out of scope. tempdb is a shared resource and you don't want it clogged up with unnecessary data for too long.
Global temporary tables (i.e. tables with names starting with ##) are visible to all users. These are dropped dropped when the session that created the table ends and all other tasks have stopped referencing them (ie. when other queries that are using them have completed). And yes, you can manually drop them with a DROP statement.
I'm not a fan of creating user objects in tempdb but if you really wanted a temporary table that stayed until the server restarts, one curious option would be to create a user table in tempdb automatically every time the system starts up. You could do that with either an Agent job (easiest) or by assigning a startup procedure (more reliable but also requires a configuration change).
Abstraction
One thing you need to decide early on though, is if temporary tables are the right way for you to pass data from one proc to another. They're certainly easy, and importantly, the data doesn't get duplicated when the sub-procedure is called.
However, for me the jury is out on whether this is a good idea or not. What you end up doing is making the sub-procedure much harder to test. It ends up referring to a table that doesn't exist anywhere in the procedure's code. That just breaks all concepts of abstraction pretty solidly.
As I said though, people like it for performance. You aren't copying the data.
An option to consider instead is creating a real table and putting the temporary data there, keyed off your session ID. Then the only issue is how it gets emptied again. That wouldn't have an automatic clean-up, and that's important if you have people creating temporary data and never removing it themselves; just letting it go out of scope.
Dropping and Recreating
Before creating temporary tables, it's common to make sure they don't already exist. In the past, you'd often see this type of code:
|
1 2 3 4 5 6 7 |
IF OBJECT_ID('tempdb..#SourceData') IS NOT NULL BEGIN DROP TABLE #SourceData; END; CREATE TABLE #SourceData ( |
Now you can just use:
|
1 2 3 4 |
DROP TABLE IF EXISTS #SourceData; CREATE TABLE #SourceData ( |
NOTE: When you create these tables, you don't provide a schema name. In fact if you do, it's ignored.
When are global temp tables needed or useful ?
I almost never use them. However, they do have uses. For example, if I wanted to "snoop" on the data that's in a temporary table in another session, I could use a global temporary table for that.
I've seen them used for licensing schemes i.e. counting the number of active sessions. But nowadays, if you can, an in-memory non-durable table would probably work better.
In most cases that I see them used, a "real" table would actually be a better solution.
Are you sure about this ?
I use temp tables in a couple of scripts and the perform really well …
Hi Joe, the issue isn't about temp tables or not. It's about global temp tables. They usually aren't what's required.
Hi Brent,
is in SQL Server a possibility to store a small "amount" of data which is used very often (e.g. application parameters) in a kind of global temp table which is stored in-mem? Without having the whole effort of using in-mem-tables…
Thanks
"Brent" ?? 🙂 Hi Juergen, both global temp and normal temp tables are disk based. They aren't "in-memory". Why not just use a table? It'll be cached as required just the same.
I had an application which ran a query joining a small table T1 in database DB1 on server S1 (owned by me) with a large table T2 in database DB2 on linked server S2 to which I only had read permission. Our InfoSec team told me I had to eliminate the linked server so I used SSIS to: copy T1 into a global temp table on S2, run a query joining the global temp table with T2, and inserted the results into a table on DB1. Somewhat kludgy, but a whole lot better than copying all of T2.
Hi Phil, if the different parts of this were done by different connections (the only reason for a global temp here), what was to keep the table in place? i.e. which session stayed connected the whole time?
Hi,
You could use "except" in your query.
insert local.tblA
select c1 from LinkedServer.tblA where ….
except
select c1 from local.tblA where ……
I use them in the context of optimizing a .Net report that was taking a long time to run. I wanted to use a normal temporary table but since .NET application pool keeps recycling the connections, I had problems where my tables where getting flushed before the report finished.
I considered loading the data into the program and then passing it on to other queries but it meant I needed to re-create a temp table for every query.
In the end, I ended up using a global temp table for the reports. It works great, and I make sure to drop the table at the end of the report.
Hi Danielle, but if you have app connections that keep getting recycled, what's to stop the global temp table from disappearing between connections? Even better, why not create a proc that does the work, and just have the .NET app call it?
I do agree in theory, but we are one of those shops with a lot of global temp tables – we have lots of ETL procedures that call other procedures and expect a global temp table to come out. Our problem is concurrency – as soon as a stored procedure gets called twice around the same time, one of the parents will be disappointed as their global temp table gets dropped by the other one.
We've tried the persisting memory-optimised table route but the performance nosedived. We haven't revisited it for a while.
Hi Dylan, that sounds like a situation that could go quite wrong. Why not just use tables?
It's a good question, Greg. I suppose it just seems such a big job no-one wants to start it.
I use a global temporary table for a routine that processes imported files in multiple parallel sessions (I use a SSIS package which calls the same procedure 6-10 times at once).
In the procedure was a heavy join (price tables etc.) with ~10 tables. To increase the perfomance (by factor 9 (!)) I added a step to the SSIS which creates a ##tempTable that prejoins the 10 tables, so all of my sessions could access them.
Of course I could have used a regular table for this too, but I would not have known, if it is outdated (and needs to be rebuild) without additional effort (as triggers or a timestamp column or using a parameter table etc.).
I tested an indexed view too, but even if I added the WITH NOEXPAND table hint, it was slower (and produced some other problems that I already forgot).
Hi Thomas, not following why a normal table wouldn't have worked just the same.
( it seems I have no idea how to format this correctly – please do so, if possible…?)
For us, the only time I've intentionally used a Global temp table was to persist a variable across multiple GOs in a single batch:
— Construct the Linked Server, if necessary
DECLARE @RequireLinkedServer bit = 1
CREATE TABLE #persist( — This allows the @RequireLinkedServer to persist across GO statements
RequireLinkedServer bit
)
INSERT INTO #persist(RequireLinkedServer)
VALUES (@RequireLinkedServer)
IF (@RequireLinkedServer=1)
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'ADSI'…
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI'…
END
GO
DECLARE @RequireLinkedServer bit; SELECT @RequireLinkedServer=RequireLinkedServer FROM #persist; IF (@RequireLinkedServer=1)
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible'…
GO
DECLARE @RequireLinkedServer bit; SELECT @RequireLinkedServer=RequireLinkedServer FROM #persist; IF (@RequireLinkedServer=1)
EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access'…
GO
etc.
Hi Daniel, you don't need "global" temp tables to retain values across multiple batches. (GO is a batch separator, so you can't have multiple GOs in a batch, but you can use it to have multiple batches in a script). Normal temp tables work fine for that. They persist across batches in a session.
Whoa!! That's a Eureka moment for me. I've always seen GO as the end of everything, but that is really interesting news. Thanks Greg!
You are most welcome. You might find this helpful: https://blog.greglow.com/2017/12/21/shortcut-using-count-go-batch-separator/
Thanks, Greg!
I could see a rare case. If you were using dynamic SQL and needed to create a temp table that needed to be accessed by another batch of dynamic SQL down the line, global temp tables might be the way to go. But that certainty would be an uncommon occurrence to say the least.
Hi Robert, and yes, who'd want to be testing that? 🙂 Even then, you could just create it before the dynamic SQL and be done with it. (Assuming you don't need the dynamic SQL to determine its schema).