Question

What is a temporary table? Compare #temp and ##temp.

Answer 1

Temporary Table:

So a temporary table can be defined as a database table as the name suggests, Also which exists on the database server temporarily.

It normally stores subsets of normal table data for a temporary period of time.

It is deleted as soon as the connection is terminated it also helps to store and process intermediate data. So it is particularly useful when we have a large amount of data also we need to interact with smaller sets of data.

So temporary tables are highly useful in the database.

#temp:

We can say #temp tables are the type of temporary tables that are available to the session that created it and is dropped when the session is closed. So #temp is used only for that session.

#temp is commonly referred to as the local temp variable which is available for SQL server connection as it is a temporary table it will be automatically deleted when the session that created the table has ended.

##temp:

So ##temp is referred as Global Temp Table(##).

So we can say Global temp tables are available to all SQL Server sessions or connections that means it is available to all the users in the database.

These can be simply created by any SQL Server connection user since these are temporary tables these are automatically deleted when all the SQL Server connections have been closed.

As we know Global temporary table name is stared with double hash ("##") sign.

So the main difference is ##temp tables (global) are available to ALL the sessions but also it is still dropped when the session that created it is closed and all of the other references to them are closed.