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.

Filed under: Blog Tagged: syndicated, T-SQL