Explicitly using tempdb with ##tables

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:

2016-04-21 13_55_14-Microsoft Solitaire Collection

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.

2016-04-21 13_56_21-Start

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

5 Responses to Explicitly using tempdb with ##tables

  1. Lynn Pettis says:

    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.

  2. way0utwest says:

    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.

    • Lynn Pettis says:

      I have seen people do this instead of using a Sandbox database. Not really sure of the pros and cons between either approach.

    • lonnyniederstadt says:

      Kimberly Tripp used the tempdb.dbo.mytable format in her stored procedures measuring data skew across statsblog statistics.

    • way0utwest says:

      Is that a permanent (or user created) table in tempdb? That’s different than tempdb.dbo.#mytable or tempdb.dbo.##mytable.

Comments are closed.