One of our stored procedure was using Cursor to update rows. Total rows to be updated varies from 1000 to 2000. In our scenario, this stored procedure was taking 50 minutes to complete the update.

I replaced the Cursor and used Table Variable. At any point of time, the Table Variable will also contain 1000 to 3000 rows. When executed the update finished in 2 minutes.

Server is Windows Server 2012 64-bit with 64GB RAM. SQL Server 2008

I want to know whether to replace Table Variable with #TempTable? In the scenario mentioned above, Table Variable is working fine.

I checked few posts on Table Variable. One post says SQL Server decides when to keep Table Variable in memory and when to move it to TempDB.

For the above configuration mentioned, when will Table Variable be moved to TempDB?

  • 3
    Probably #temp table will be the same or better than @table variable once you hit a substantial amount of rows (the actual number will vary depending on index(es), width of rows, etc). Benefits of #temp tables in your case are probably mostly that they can have statistics and that they can be cached and reused. Sep 6, 2015 at 0:54
  • @AaronBertrand Is it a myth that Table Variable is in-memory?
    – RPK
    Sep 6, 2015 at 17:06
  • 1
    Yes, please read the duplicate in full. Sep 6, 2015 at 17:58

1 Answer 1


Actually Temp table and Table variable use tempdb (Created on Tempdb). Please check the below code which I will use to create Temp Table and Variable Table. and check where they were created.



-- Create Temp Table and Table Variable

(CandidateID INT)

DECLARE @TempVairable TABLE (EmployeeID INT)

-- Check Where Temp Table and Table Vairable Created
SELECT * FROM tempdb.sys.all_objects
WHERE type = 'U' -- User Table
AND create_date >= @DtmStartDateTime 

-- This Filter will be used to check the     new tables


enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.