1

This question already has an answer here:

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?

marked as duplicate by Paul White Sep 6 '15 at 7:01

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 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. – Aaron Bertrand Sep 6 '15 at 0:54
  • @AaronBertrand Is it a myth that Table Variable is in-memory? – RPK Sep 6 '15 at 17:06
  • 1
    Yes, please read the duplicate in full. – Aaron Bertrand Sep 6 '15 at 17:58
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.

BEGIN TRAN

DECLARE @DtmStartDateTime DATETIME = GETDATE()

-- Create Temp Table and Table Variable

CREATE TABLE #TempTable
(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


ROLLBACK

enter image description here

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