#temp table or a @table variable

In a stored procedure, you often have a need for storing a set of data within the procedure, without necessarily needing that data to persist beyond the scope of the procedure. If you actually need a table structure, there are basically four ways you can "store" this data: 


1.local temporary tables (#table_name), 
2.global temporary tables (##table_name),
3.permanent tables (table_name), and
4.table variables (@table_name). 




Local Temporary Tables :
 

CREATE TABLE #people  (      id INT,      name VARCHAR(32)  )

A temporary table is created and populated on disk, in the system database tempdb
The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it: 
 

DROP TABLE #people

EXAMPLE:

CREATE TABLE #TibetanYaks(
YakID int,
YakName char(30) )

INSERT INTO #TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM  dbo.Yaks
WHERE  YakType = 'Tibetan'

-- Do some stuff with the table

drop table #TibetanYaks

Table Variables  :
A table variable is created in memory, and so performs slightly better than #temp tables
1.Table variables don't need to be dropped when you are done with them.
2.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.
3.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 (which can be a good thing or a bad thing; remember "release early"?).
4.Table variables are the only way you can use DML statements (INSERT, UPDATE, DELETE) on temporary data within a user-defined function. You can create a table variable within a UDF, and modify the data using one of the above statements. For example, you could do this: 
5.we can use the  temporary table  with in function

DECLARE @people TABLE  (      id INT,      name VARCHAR(32)  )

CREATE FUNCTION dbo.example1  (  )  RETURNS INT  AS  BEGIN      DECLARE @t1 TABLE (i INT)      INSERT @t1 VALUES(1)      INSERT @t1 VALUES(2)      UPDATE @t1 SET i = i + 5      DELETE @t1 WHERE i < 7        DECLARE @max INT      SELECT @max = MAX(i) FROM @t1      RETURN @max  END  GO

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM  dbo.Yaks
WHERE  YakType = 'Tibetan'

-- Do some stuff with the table 

DECLARE @TibetanYaks TABLE (
YakID int,
YakName char(30) )

INSERT INTO @TibetanYaks (YakID, YakName)
SELECT  YakID, YakName
FROM  dbo.Yaks
WHERE  YakType = 'Tibetan'

UPDATE  @TibetanYaks
SET  YakName = UPPER(YakName)

SELECT *
FROM @TibetanYaks