CODEWITHSUNDEEP
sql-server
Craig Schwarze
Craig Schwarze

Reputation: 11625

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

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

Upvotes: 111

Views: 169251

Answers (6)

Arnkrishn
Arnkrishn

Reputation: 30434

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

Upvotes: 142

Markus
Markus

Reputation: 1211

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.

Upvotes: 8

Adriaan Stander
Adriaan Stander

Reputation: 166406

Have a look at

Upvotes: 29

Schmed
Schmed

Reputation: 51

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.

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280330

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.

Upvotes: 5

whiner
whiner

Reputation: 134

# 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

Upvotes: 7

Related Questions