Can I request SQL Server to cache a certain result set?

In short, no: not at the SQL server end; it will of course load the data into memory if possible, and cache the execution plan so subsequent calls may be faster but it can't cache the results Options: tune the plan; the sort sounds aggressive - could you perhaps denormalize some data or add an index (perhaps even a clustered index); there may be other things we can do with the query if you show it (but tuning without a fully working DB is guestimation at best) cache the results at the web-server if it is sensible to do so.

In short, no: not at the SQL server end; it will of course load the data into memory if possible, and cache the execution plan - so subsequent calls may be faster, but it can't cache the results. Options: tune the plan; the sort sounds aggressive - could you perhaps denormalize some data or add an index (perhaps even a clustered index); there may be other things we can do with the query if you show it (but tuning without a fully working DB is guestimation at best) cache the results at the web-server if it is sensible to do so.

Whilst you can create an indexed view, as you allude to in your update, you should be aware that: There are quite a lot of rules you have to follow, both when creating the view, and when updating the tables upon which it is based, and, Just because there's a (clustered) index, that doesn't imply a sort order - you would still have to use an ORDER BY when querying this table, and, Unless you're using Enterprise edition, you have to query the table with the WITH (NOEXPAND) query hint You would specify the order for the index by specifying ASC and DESC in the CREATE INDEX statement, not in the CREATE VIEW. The "hack" to allow ORDER BY in a view (by specicfying top 100 percent) would have no effect.

Unbelievable! Thanx a lot for the information :) – Ranhiru Cooray Nov 4 '10 at 11:04.

Again, SQL server itself will cache frequent queries in memory. You can test this by using query analyzer and running a complex query a few times, each time it will get faster. Given this a permanent cache might not be necessary.

If it is I recommend using a cache table and running your query and inserting the values into the other table. You can either use a application variable or another sql table to determine when to refresh the cache again. Example query for inserting into the cache table: Insert Into Cache Select Value, Value from Table 1 Order by Field.

An option that is used sometimes is to store the sorted data in a secondary table. This could e.g. Be a temporary table which you create for just the session, or a cache table for the entire database.

Check this link. It may help you :) msdn.microsoft.com/en-us/library/ee34398...).aspx.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.