It looks like you're new here. If you want to get involved, click one of these buttons!
I'm learning more about table variables. It states that temp tables are always on disc, whereas table variables are in memory, implying that table variables perform better than temp tables since table variables utilise less IO operations than temp tables.
However, if there are too many records in a table variable that cannot be stored in memory, the table variable, like the temp table, will be saved to disc.
But I'm not sure what "too many records" means. 100,000 documents? or a million records? How can I determine whether a table variable I'm using is in memory or on disc? Following on from this post, is there a function or tool in SQL Server 2005 that can measure the scale of a table variable or notify me when a table variable is written to disc from memory?