If I have a table, temporary or otherwise, which I want to place in SQL Server memory, is there a way to *force* SQL to do that? If the answer is "SQL handles the memory and will keep it in memory if possible, so don't worry with it" then is there a practical way to verify that the table is indeed in memory? Will it stay there if it is in memory or disappear as soon as there is another server request? Also, if there is an associated index with the table, will the index automatically be in memory also?
There used to be a way to pin a table into memory, but it caused all kinds of problems, so Microsoft removed it from the product. Now, SQL Server manages that memory, and as you say, it will keep it there if possible. It will stay in memory as long as it keeps getting accessed and no other query comes along that needs to dump it out of memory in order to free up space. No, the index won't automatically be in memory. Only if it's accessed and read into the cache. I'm not aware of a way to monitor this except to watch for physical i/o versus logical i/o in the queries.
Sure - write a bad query that ensures table and index scans and run it every five minutes :) You may "push" valid data out of the buffer but you will have effectively pinned the table into RAM. (Totally Tongue-In-Cheek!)