Rakesh Singh

Rakesh Singh

  • NA
  • 180
  • 54k

What is #temp table and @table variable in SQL Server

Apr 24 2014 1:57 AM

#temp Table (Temporary Table)
temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.

The data in this #temp table (in fact, the table itself) is visible only to the current scope. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.
Syntax:

-- create temporary table

CREATE TABLE #myTempTable (

AutoID int,

MyName char(50) )

-- populate temporary table


INSERT INTO #myTempTable (AutoID, MyName )

SELECT AutoID, MyName


FROM myOriginalTable

WHERE AutoID <= 50000

-- Drop temporary table

drop table #myTempTable

@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.
Syntax:

DECLARE @myTable TABLE (

AutoID int,

myName char(50) )

INSERT INTO @myTable (AutoID, myName )

SELECT YakID, YakName

FROM myTable

WHERE AutoID <= 50

We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.


Answers (1)