11

I'm inserting over 10 million rows into a temp table and its size is about 5GB. My computer has 32GB of RAM. Does SQL Server put this table in RAM or on the disk?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MRueda
  • 111
  • 1
  • 3
  • 1
    Temp tables will be stored in ram as much as possible but they spill over to disc when needed. – Sean Lange Jul 28 '17 at 18:35
  • Are you concerned about performance that could easily be improved by spending a few bucks on hardware? [Get a huge SSD](https://www.amazon.com/SanDisk-Ultra-1TB-SATA-SDSSDHII-1T00-G25/dp/B01N1862SW/ref=sr_1_1?s=pc&ie=UTF8&qid=1501267502&sr=1-1&keywords=1tb+ssd). In the words of Jeff Atwood, one of StackOverflow's founders, ["Hardware is Cheap"](https://blog.codinghorror.com/hardware-is-cheap-programmers-are-expensive/) – Bob Kaufman Jul 28 '17 at 18:42
  • 8
    temp tables are **always** stored on disk.look out this extensive answer on dba,se :https://dba.stackexchange.com/questions/16385/whats-the-difference-between-a-temp-table-and-table-variable-in-sql-server and also on SO:https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server – TheGameiswar Jul 28 '17 at 18:48
  • 1
    Not all hardware is cheap--specifically SSD at larger sizes. – S3S Jul 28 '17 at 18:48
  • [Agreed](https://www.theverge.com/circuitbreaker/2016/8/10/12424666/seagate-60tb-ssd-worlds-largest) @scsimon. But even at the extreme, they're usually a cheaper solution than investing in programmer-hours to make a system more efficient. – Bob Kaufman Jul 28 '17 at 18:52
  • 1
    Definitely not arguing the cost benefit against hiring developers, but I just didn't see how that played a part in the OP question is all (since it wasn't even about performance) @BobKaufman. Good links from you though for the OP. – S3S Jul 28 '17 at 18:54
  • So... our conclusion is: hardware is cheap and temp tables are stored in tempdb. – Jacob H Jul 28 '17 at 18:59
  • sometimes, and all the time @JacobH hahaha :) – S3S Jul 28 '17 at 19:00
  • 3
    It's always stored on data pages in `tempdb` but that doesn't mean the pages are actually written to disc. See the end of the dba.se answer for info about that (under "Memory Only?") – Martin Smith Jul 28 '17 at 20:44

0 Answers0