Answer: #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 (
MyName char(50) )
-- populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT AutoID, MyName
WHERE AutoID <= 50000
-- Drop temporary table
drop table #myTempTable
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.
DECLARE @myTable TABLE (
myName char(50) )
INSERT INTO @myTable (AutoID, myName )
SELECT YakID, YakName
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 |