CODEWITHSUNDEEP
sqlsql-serverdatabase-administration
Tino Jose Thannippara
Tino Jose Thannippara

Reputation: 782

Can we create Temp Tables in File Storage, Not in Memory

My data set is too large (about 12 million records) to query. I need to fetch that much amount of data for intermediate calculations. If I use Temp tables, which causes a huge memory consumption.

Is there any mechanisms in SQL Server 2008 R2 onward, which can create temp tables, not in memory, in disk ?

Upvotes: 1

Views: 333

Answers (1)

Arkadiusz
Arkadiusz

Reputation: 489

If you declare table like variable 

declare @table ...

It is stored in memory. To store it in db file create temporary table like normal table with # prefix for only your session or ## prefix for all sessions like this:

CREATE table #TMP (
   name nvarchar(20)
   ....
)

You can create it automaticly doing something like this:

select id, name, something_else into #TMP from your_phisical_table

Upvotes: 1

Related Questions