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?