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

Question

There is a certain query that is being called from an ASP .NET page. I studied 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?

Answer 1

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.