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

 Posted by Raja on 12/14/2008 | Category: Sql Server Interview questions | Views: 21073

#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.


-- create temporary table

CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )

-- populate temporary table
INSERT INTO #myTempTable (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.



AutoID int,
myName char(50) )

INSERT INTO @myTable (AutoID, myName )
FROM myOriginalTable
WHERE AutoID <= 50

-- to select the data from Temp variable

SELECT * FROM @myTable

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

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: G on: 9/30/2013 | Points: 10

Login to post response