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

There is a certain query that is being called from an ASP .NET page. I studied 开发者_StackOverflow社区the execution plan of that query in Management Studio and 87% is for a sort. I badly need the sorting or else the data displayed would be meaningless.

Is there anyway that I can request SQL Server to cache a sorted results set so it will return the data faster in consequent runs?

Or is SQL Server smart enough to do the cache handling and am I doing mistake by trying to force it to cache results, if that is possible?

Any relevant information will be highly appreciated and thanks a lot in advance :)

UPDATE:

I just read in an article that creating a View with a clustered index will increase performance because the index will persist the data in a view to disk. Is this true? How do i get about doing this? Any articles?

---

Whilst you can create an indexed view, as you allude to in your update, you should be aware that:

1. 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,
2. 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,
3. Unless you're using Enterprise edition, you have to query the view with the WITH (NOEXPAND) query hint
4. 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.

---

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

---

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.

---

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