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?

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, T-SQL, temp tables