Temp table with ### (ternary) or more hashes
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 temporary table? How can I check it? When I tried to execute select
with:
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 the third case is true, doesn't that mean it's a generic table with a name ###MyTable
? And won't I see this table in my SSMS table explorer like any other physical table?
What happens if I start adding multiple # (hashes) to my table name?
source to share
This is . It treats the third as part of the tab name. If you check , you will see a table with no session id. If the temp table is created as a local temp table, you can see that the specific one will be added with the temp name, since there is no one added with the name temp tablename, it is . global temp table.
#
temdb database
sessionID
session ID
global temp table
CREATE TABLE ###MyTable
(
IntColumn INT,
ValueColumn VARCHAR(100)
)
After executing the above query
GOTO Server -> Databases -> System Database -> Tempdb -> Temporary Tables
you can find the created Global temptable
one as shown in the picture below.
source to share
The triple hash table is a normal global temp table where the third hash has become part of the table name rather than doing something 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 running the results shows that all 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 will only be able to access the prefixed global temporary tables ##
:
-- this one is accessible
SELECT *
FROM ###temp
-- this one is accessible
SELECT *
FROM ##temp
-- this one won't work as it out of scope in a new query window
SELECT *
FROM #temp
Finally, anything after the second hash becomes part of the table name will be stored as global temp table.
source to share