Temp table with ### (triple) or more hashes

Question

We know that in SQL Server, creating a table with # means "local temp table" and ## means "global temp table".

But when I create a table like below:

create table ###MyTable(IntColumn int, ValueColumn varchar(100))

Is this table a local or global temp table? How can I test it? When I tried to select it via:

Select * from #MyTable -- SQL said it doesn't exists
Select * from ##MyTable -- SQL said it doesn't exists
Select * from ###MyTable -- I get the output

If third case is true, doesn't it mean that this is general table with the name ###MyTable? And wouldn't I see this table in my SSMS table explorer like every other physical table?

What will happen if I start adding multiple # (hashes) before my table name?

Answer 1

It is a global temp table. It is considering the third # as part of the tablename. If you check the temdb database you can see the table without the session ID. If the temp table is created as local temp table then you can see the particular sessionID will be appended with the temptable name, Since there is no session ID appended with the temp tablename it is a global temp table.

CREATE TABLE ###MyTable
  (
     IntColumn   INT,
     ValueColumn VARCHAR(100)
  ) 

After running the above query

GOTO Server -> Databases -> System Database -> Tempdb -> Temporary Tables

you can find the created Global temptable just like the below image.

Answer 2

The table with the triple hash is just a regular global temp table, where the third hash has has become part of the table name as opposed to doing anything special.

-- global temp table name: #temp
SELECT 1 AS value
INTO ###temp

-- global temp table name: temp
SELECT 2 AS value
INTO ##temp

-- temp table name: temp
SELECT 3 AS value
INTO #temp

select * from   tempdb.INFORMATION_SCHEMA.TABLES 

The above SQL when executed results shows that all of the objects are added to temp db as you would expect.

To test the theory, if you run the following in a new query window you should only be able to access global temp tables with the ## prefix:

-- this one is accessible
SELECT *
FROM ###temp

-- this one is accessible    
SELECT *
FROM ##temp

-- this one won't work as it's out of scope in a new query window
SELECT *
FROM #temp

In summary, anything after the second hash becomes part of the table name and it will be saved as a global temp table.