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.