• RAM-only table-variables are indeed a myth.

    Table variables (like temp tables) will be in memory if possible and written to disk if not. They are always allocated space in TempDB, whether they're actually written down and removed from memory depends on what's happening (memory pressure, size of tables, etc)

    They may well be written to disk even if there isn't memory pressure (though in that case the pages likely will remain in memory as well). That's, as far as I know, the lazy writer being proactive (the checkpoint doesn't write out dirty pages for TempDB)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass