Table variable for large amount of data [duplicate]

Question

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?

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.

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