How to load a table into memory ?

How can I load a table into memory in SQL Server 2000 or SQL Server 2005 ?

How can I keep the table always in memory in SQL Server 2005 ?

with all my thanks

You want to keep the table in memory..? May I know the purpose of the this. or please put your requirment clearly..

|||

shuaixf:

How can I load a table into memory in SQL Server 2000 or SQL Server 2005 ?

How can I keep the table always in memory in SQL Server 2005 ?

with all my thanks

If you are in SQL Server 2000 look up DBCC PINTABLE that will let you keep a regular table in memory very crude programming so it was dropped in 2005, what you can do now is to use local temp table #, global temp table ## longer scope and CTE ( common table expression ) these are new virtual in memory views from ANSI SQL just implemented by Microsoft. There is nothing valid you are doing that is not covered by the last three. So run a search all of the above in the BOL(books online) and try the links below for some valid tricks with temp table and Microsoft documentation of CTE. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

http://msdn2.microsoft.com/en-us/library/ms175972.aspx

|||

First let me appreciate your answer

There is a table(user table ) in my db ,which used much frequently , in which there are about 300 thousand records

My server memory is 5G

Could you give me some suggestions to optimize the buffer cache ? and how to do it ?

By the way , what is the organization of the buffer cache in SQL Server?

thanks!

|||

That is performance tuning internals explained in details in the first link and the last two links are Microsoft docs covering the Buffer but there is another cache that affects your performance the procedure cache. Try the links below for details and the first covers more volume than you have. Hope this helps.

http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp

http://msdn2.microsoft.com/en-gb/library/ms189628.aspx

http://msdn2.microsoft.com/en-gb/library/ms177441.aspx