How to implement paging in an effective way?
The query will be executed every time. SQL Server does not cache results.

Proper paging won't be implemented until SQL Server 2011, but until then your options (as you identified) are:


ROW_NUMBER() and queries on demand
client caching


A prepared SQL statement will be executed every time too.

If you have a fat client then caching is local to the client. This is mostly OK unless you have millions of blobs for example.

In our web client we render all the results but only show the top 100 and have a "Show all" button than expands a hidden DIV with rows 101+. We don't cache in the web server and we don't offer paging.