Store temporary data in Stored Procedure in SQL

We  often have a problem to store a set of temporary data within the procedure, which not persist beyond the scope of the procedure. In such situation, we have basically four ways through which we can "store" this temporary : local temporary tables (#table_name), global temporary tables (##table_name), permanent tables (table_name), and table variables (@table_name). 

     Each of the four table options has its own purpose and use, and each has its benefits and issues:

1. Normal tables are exactly that, physical tables defined in your database.
2. Local temporary tables are temporary tables that are available only to the session that created them. These tables are automatically destroyed at the termination of the procedure or session that created them.
3. Global temporary tables are temporary tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. Both local temporary tables and global temporary tables are physical tables created within the tempdb database.
4. Table variables are stored within memory but are laid out like a table. Table variables are partially stored on disk and partially stored in memory. It's a common misconception that table variables are stored only in memory. Because they are partially stored in memory, the access time for a table variable can be faster than the time it takes to access a temporary table.

Temporary Table

We prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. 

Example of Temp table:

CREATE TABLE #temp (

ID int,

FirstName varchar(30),

LastName varchar(30) )

INSERT INTO #temp (ID, FirstName,LastName)

values(1,'Pankaj','Rana')

select * from #temp

drop table #temp

Table Variables

If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. Table variables are automatically cleared when the procedure or function goes out of scope, so you don't have to remember to drop or clear the data

Example of Table variable:

DECLARE @temp TABLE (

ID int,

FirstName varchar(30),

LastName varchar(30) )

INSERT INTO @temp (ID, FirstName,LastName)

values(1,'Pankaj','Rana')

select * from @temp

Note: Table variables don't need to be dropped when you are done with them.

Which to Use

·         If you have less than 100 rows generally use a table variable.  Otherwise use  a temporary table.  This is because SQL Server won't create statistics on table variables.

·         If you need to create indexes on it then you must use a temporary table.

·         When using temporary tables always create them and create any indexes and then use them.  This will help reduce recompilations.  The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.