Difference between #temptable and ##TempTable?

问题:

Can you please explain difference between #temptable and ##TempTable in SQL Server. I googled about this but can't find any answers.

Please help me regarding this

回答1:

Local temp tables

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Example:

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

scope of Local Temporary table is only bounded with the current connection of current user.

Global Temporary tables

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Example:

CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

Vignesh you can know more about this here

回答2:

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

回答3:

Here is another quick overlook of SQL Temporary Tables, if you would google, you could get to know more about it

Local Temp Table

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

Both, Temporary tables are stored inside the Temporary Folder of tempdb. Whenever we create a temporary table, it goes to Temporary folder of tempdb database.

Temporary Table with specific ID

When you create Temp table, SQL server creates table name along with specific ID, so it wont collide with another Table Table name

primarily sourced from

回答4:

Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

Taken from here

More on this

回答5:

TempTables aren't just local to the User, or connection. They are local to the process that created it and any processes that the creating process spawns. For example of I have the following:

Declare @strDynamicSQL as varchar(8000)

Set @strDynamicSQL = 'Select GetDate() As TheDate Into #TheDateTable' Execute(@strDynamicSQL)

Select * From #TheDateTable

I get the following error: Msg 208, Level 16, State 0, Line 7 Invalid object name '#TheDateTable'.

But if I do:

Declare @strDynamicSQL as varchar(8000)

Create Table #TheDateTable ( TheDate DateTime )

Set @strDynamicSQL = 'Insert Into #TheDateTable Select GetDate() As TheDate' Execute(@strDynamicSQL)

Select * From #TheDateTable

I get no errors.

In the first example the "Execute" statement happens in a spawned process. Since the table is created in that process when it returns that process goes away. And with the process the table is "bye-bye".

In the second example the table is created by the top level process. It's then interacted with in the spawned process. The table is available to process that it was created in and any process it spawns.

tables break this. The process a ## table is created in will be the controlling process. The table will not get flagged for removal if this process is still alive even if there are not tasks against that process. Once the process that the ## table was created in goes away, the table is tagged for removal when the last task is executed against it.

A simple way to see it is # tables are available only the the scope of the process that it was created in. ## are available in the same way as any other table except that the existence is comes and goes with the process it was created in.