The Temporary Tables Of SQL Server

Within SQL Server there are 5 different temporary tables that you can use to store data.

They are :-

Table Variables

DECLARE @Table TABLE(

name varchar(30) NOT NULL, 
location varchar(30) NOT NULL 
);

Tables Values Parameters

CREATE TYPE LocationTableType AS TABLE 

( LocationName VARCHAR(50)
, CostRate INT );

Common Table Expressions (CTE)

-- Define the CTE expression name and column list.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO
Local Temporary Tables
SELECT *  INTO #Temp1 FROM Table1
Global Temporary Tables
SELECT * INTO ##Temp2 FROM Table1

Local Temporary Tables is the most common type of temporary table to use but I have been using CTE's more often now if I need to do extra work with the result set.

Local Temporary Tables are good if you want to do multiple processing on a set of data. CTE are good for readability or recursive processing.

Global Temporary Tables, I don't really use them much because I have never seen a business case to use one.

Tables Values Parameters are good when used in combination with a .NET application because you can pass an entire result set back to the calling program in one go.

Table Variables are very similar to Local Temporary tables but have these differences

Item	#Temp Tables	@Table Variables
Can participate in a transaction	YES	NO
Writes to Log File	YES	YES
Writes only to memory (not disk)	NO	NO
Can Qualify for Parallelism	YES	NO
Allows creation of statistics	YES	NO
Does not affect recompilation	NO	YES
Allows nonclustered indexes	YES	NO
Can perform SELECT INTO	YES	NO
Can access in nested stored procedures	YES	NO
Can define globally	YES	NO
Can use in user defined functions	NO	YES
Can insert from EXEC	YES	YES
Allows TRUNCATE	YES	NO
Allows ALTER TABLE	YES	NO

so this is a basic summary of using temporary tables in SQL Server and should be a start for you to us them.