SQL Server tables: what is the difference between @, # and ##?

Question

In SQL Server, what is the difference between a @ table, a # table and a ## table?

Answer 1

Hi

#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.

cheers

Answer 2

# 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.

For more: http://www.sqlteam.com/article/temporary-tables

Answer 3

Have a look at

• Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance
• Differences between SQL Server temporary tables and table variables
• Temp Tables and Table Variables: When To Use What And Why
• Should I use a #temp table or a @table variable?

Answer 4

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.