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.