Temp table quiz
10June 26, 2017 by Kenneth Fisher
Brent Ozar (b/t) posted a pop quiz on twitter earlier today.
Go ahead and give it a shot .. I’ll wait.
So? What do you think? Did you get it right? I did, but I wasn’t 100% certain, nor did I initially think through all of the implications. The question actually has more depth to it than you might think on the surface. So I thought it would be fun to go through what I was thinking before I made my decision, what actually happens, and what I realized afterward.
So starting with a first glance I saw the [#TempTableOrRealTable]. So obviously the first question is are we going to end up with a temp table or a real table? Brackets ([]) are used if you want to create table names with odd characters, but on the other hand, if the first character of a table is a pound (#) then it’s a temp table. I was pretty sure (90+%) that even with the []’s it was still going to be a temp table. Ok! So far so good! We are dealing with temp tables.
Next question. What about the database names? Well, all temp tables go into tempdb. No exceptions. (Unless you know something odd that I don’t?) So either we are going to get an error, or we are going to ignore the database name. I guessed that it was going to ignore the database name, but the DBname probably had to exist. Of course, then the question is did Brent mean for me to run it with that DBName or just pick one from my list? Let’s start with one from the list and see how it goes.
CREATE TABLE Test.dbo.[#TempTableOrRealTable] (ID INT);
Database name ‘Test’ ignored, referencing object in tempdb.
Pretty much what I expected. But at this point, I had a bit of an ah ha moment. I realized the database name doesn’t matter at all. It could exist or not. A little later I realized the schema isn’t going to matter either. That being the case the whole script should run just fine. Because the database and the schema are ignored we are working with a single temp table throughout.
CREATE TABLE harder.dbo.[#TempTableOrRealTable] (ID INT); INSERT INTO better.dbo.[#TempTableOrRealTable] (ID) VALUES (1); SELECT * FROM faster.dbo.[#TempTableOrRealTable]; DROP TABLE strong.dbo.[#TempTableOrRealTable];
If you run the whole thing at once you get this:
Database name ‘better’ ignored, referencing object in tempdb.
(1 row(s) affected)
Database name ‘faster’ ignored, referencing object in tempdb.
ID
———–
1(1 row(s) affected)
Hmm, we appear to be missing some warnings. If you run each line separately (or add some GOs) you get this:
Database name ‘harder’ ignored, referencing object in tempdb.
Database name ‘better’ ignored, referencing object in tempdb.(1 row(s) affected)
Database name ‘faster’ ignored, referencing object in tempdb.
ID
———–
1(1 row(s) affected)
Database name ‘strong’ ignored, referencing object in tempdb.
I honestly have no idea why the warnings for harder and strong are ignored in a single batch but there you go. Anyone have any thoughts?
SQL Studies 
Oh it gets even crazier than that. Run these one line at a time:
CREATE TABLE harder.dbo.#TempTableOrRealTable (ID INT);
/* The first insert gives you a warning about ignoring the database name: */
INSERT INTO better.dbo.[#TempTableOrRealTable] (ID) VALUES (1);
/* But subsequent inserts in a row do not! */
INSERT INTO better.dbo.[#TempTableOrRealTable] (ID) VALUES (1);
/* The first select gives you a warning about ignoring the database name: */
SELECT * FROM faster.dbo.[#TempTableOrRealTable];
/* But subsequent selects in a row do not! */
SELECT * FROM faster.dbo.[#TempTableOrRealTable];
/* Hop back to an insert and the warning comes back: */
INSERT INTO better.dbo.[#TempTableOrRealTable] (ID) VALUES (1);
/* But hop back to a select, and … still no warnings. */
SELECT * FROM faster.dbo.[#TempTableOrRealTable];
This is not a good time of the year for me to try to figure stuff like this out. The heat does bad things to my brain. That’s my excuse and I’m sticking to it!
[…] Kenneth Fisher answers a Brent Ozar pop quiz regarding temp tables: […]
Top of my head without a computer next to me… Both missing warnings are DDL statements. They should run separately (using GO). Can that be the mysterious reason?
Delete & insert are DML and dml operations are throwing warning messages if you do not explicitly saying not …
I also think it’s something to do with the master DB being affected (i.e. DDL statements) and sessions (stored in tempdb too?)
Actually master isn’t affected at all by this particular set of statements. Only tempdb. 🙂
I think – on first insert the statement is parsed, compiled, and data access path is chosen during which you see the warning – on subsequent inserts using the *same* literal value the optimizer sees the cached execution plan and so does not need to reoptimize (thus no warning). Using a *new* literal value (2, 3, etc. instead of 1) causes the warning to reappear (only once though, of course, as the new statement is then cached as well).
Removing the cached plan (DBCC FREEPROCCACHE) for a specific literal and then rerunning the statement causes it to warn again (but of course it is then cached afterwards so stops warning subsequently).
For some reason couldn’t reproduce the issue where running a select statement caused subsequent inserts to rewarn….
Interesting! I’m not sure it explains the difference between running it as a single batch vs multiple batches. Cached queries run across batches as far as I know?
Ah – good point, my comment was more in response to Brent’s comment regarding the interesting behavior of the warnings on inserts and selects. I’m also curious about why the DDL statements don’t throw warnings when run as a single batch, yet emit the warnings when run individually.