In SQL Server, what is the difference between a @ table, a # table and a ## table?
This question is related to
sql-server
#table refers to a local (visible to only the user who created it) temporary table.
##table refers to a global (visible to all users) temporary table.
@variableName refers to a variable which can hold values depending on its type.
# and ## tables are actual tables represented in the temp database. These tables can have indexes and statistics, and can be accessed across sprocs in a session (in the case of a global temp table, it is available across sessions).
The @table is a table variable.
if you need a unique global temp table, create your own with a Uniqueidentifier Prefix/Suffix and drop post execution if an if object_id(.... The only drawback is using Dynamic sql and need to drop explicitly.
I would focus on the differences between #table and @table. ##table is a global temporary table and for the record in over 10 years of using SQL Server I have yet to come across a valid use case. I'm sure that some exist but the nature of the object makes it highly unusable IMHO.
The response to @whiner by @marc_s is absolutely true: it is a prevalent myth that table variables always live in memory. It is actually quite common for a table variable to go to disk and operate just like a temp table.
Anyway I suggest reading up on the set of differences by following the links pointed out by @Astander. Most of the difference involve limitations on what you can't do with @table variables.
CREATE TABLE #t
Creates a table that is only visible on and during that CONNECTION the same user who creates another connection will not be able to see table #t from the other connection.
CREATE TABLE ##t
Creates a temporary table visible to other connections. But the table is dropped when the creating connection is ended.
Source: Stackoverflow.com