I had someone ask me last night if this statement would create a permanent table in tempdb with a strange name or a global temp table:
CREATE TABLE tempdb..##mytable
( id int
);
My suspicion was that this would always be a temp table, but since I usually don’t include tempdb, I decided to test things. I quickly opened up SSMS and tried it. I got this message:
That seems to imply what I suspected. The ## override everything and determine the table type. When I look in the list of tables, I see my table there as a temporary one.
This is the behavior I’d expect, and it acts the same in SQL 2012, 2014, and 2016 (RC2).
I don’t think there’s an advantage to using tempdb..##mytable, or even creating a permanent table (until restart) using tempdb.dbo.mytable, but if there is, please note something in the comments.
Voice of the DBA 
Comments
5 responses to “Explicitly using tempdb with ##tables”
I appreciated your quick test regarding CREATE TABLE tempdb..##table at the Colorado Springs SQL Server Users Group meeting. Reading your post above you mentioned using tempdb.dbo.tablename, which creates a permanent table in tempdb versus a temporary table, local or global.
LikeLike
I didn’t mean to imply that tempdb.dbo.table creates a temp table. I’ll reword.
There are probably reasons you’d do this, but I haven’t run across any.
LikeLike
I have seen people do this instead of using a Sandbox database. Not really sure of the pros and cons between either approach.
LikeLike
Kimberly Tripp used the tempdb.dbo.mytable format in her stored procedures measuring data skew across statsblog statistics.
LikeLike
Is that a permanent (or user created) table in tempdb? That’s different than tempdb.dbo.#mytable or tempdb.dbo.##mytable.
LikeLike