What does the suffix of a temporary table's name mean?

Question

If you expand the tempdb node and go to Temporary Tables, you will notice the name of the temporary tables have "______XXXXX" appended. Where does that part refer to?Is it the memory place where the temporary table is created? Thank you

Answer 1

Temporary tables are physical tables as well, just like any other table. They are just a bit special and entail automatic cleanup when they go out of scope. To avoid name clashes, since everyone wants their temporary table to be named #tmp, SQL Server generates a pseudo-random name to be given to the physical table that gets created in tempdb. This is associated to your reference to the temporary table, e.g. SPID 1234 "#tmp" refers to tempdb.dbo.#tmp___________0000000001CA. Note also that even though you can see the table in tempdb.sys.tables from another session, it doesn't mean you can select from it the normal way, e.g.

create table #tmp (a int); insert #tmp values (1),(2);
select * from [#tmp_____________0000000001CA]  -- run by creator of #tmp

Results: 

a
-
1
2

select * from [#tmp_____________0000000001CA]  -- run by another session

Msg 208, Level 16, State 0, Line 1
Invalid object name '#tmp____________0000000001CA'.

There is no inherent meaning to the XXXXX.