Temp Tables in SQL Server 2008 r2:

In SQL, developers may come across complex JOIN quires and large amount transactions in a SP, we can handle such type of queries by using Temp table concept.
 
Temporary tables are used by every DB developer, they can improve your code's performance and maintainability. They are used most often to provide workspace for the intermediate results when processing data within a procedure. Once finished with their use, they are discarded automatically or you can write in code itself.

If you are doing more complex processing on temporary data or likely to use more than reasonably small amounts of data in them, then  local temporary tables are likely to be  a better choice. These tables are created inside tempdb database.


SQL Server provides two types of temp tables based on the behavior and scope of the table. Those are:

• local temporary tables (starting with #)
• global temporary tables (starting with ##)

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 the instances. Local temporary table name is stared with hash ("#") sign.

Global Temp Table

Global Temporary tables are available to any user by any connection until all the connections have been closed. Global Temp Table name starts with a double hash ("##").

USE AdventureWorks
GO
CREATE TABLE ##GlobalTemp_Table(
ID INT,
Name VARCHAR(50),
City VARCHAR(150))
GO

NOTE: The scope of a global temp table is available for all connections.