sql-server 2008, differences between tables

Question

What are the differences between the following 3 in sql-server 2008?

#Table_name
##Table_name
@Table_name

http://sqlserverplanet.com/tsql/yet-another-temp-tables-vs-table-variables-article — Tim Schmelter, Feb 21 '13 at 16:10
@TimSchmelter That repeats some inaccuracies found elsewhere on the web, the most glaring being the claim that operations on table variables are not logged. I commented to that effect a couple of months ago and my comment was never published. I believe my answer here to be one of the most accurate and comprehensive comparisons. http://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server/16386#16386 — Martin Smith, Feb 22 '13 at 15:27

score 1 · Accepted Answer · answered Feb 21 '13 at 16:12

From MSDN: Database Identifiers (reformatted to better suit the question).

Certain symbols at the beginning of an identifier have special meaning in SQL Server.

@: A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object.
#: An identifier that starts with a number sign denotes a temporary table or procedure.
##: An identifier that starts with double number signs (##) denotes a global temporary object. Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice.
@@: Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.

thank you for the detailed and prompt response – Peter Feb 21 '13 at 16:13 — Peter, Feb 21 '13 at 16:13

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

See this Question

#table = temp table tied to the spid
##table = global temp table
@table = table variable

edited May 23 '17 at 12:16

Community

answered Feb 21 '13 at 16:11

Lance

score 0 · Answer 3 · answered Feb 21 '13 at 16:19

#table_name - Local Temp Table

It exists only for the duration of a user session or the procedure that created the temporary table
When the user logs off or when the procedure that created the table completes, the local temporary table is lost

##table_name - global temporary table

It exists for the duration of a user session
When the last user session that references the table disconnects, the global temporary table is lost

@Table_name - table-variable - It also exists only for the duration of a user session

Temp Table and Table Variable — both are created in TempDB and not in memory

3 Answers3