Help with optimizing query

Hi, i was wondering whether some SQL expert would be willing to take a look at this query for me. It is a search query that is frequently used on our site so it needs to perform great.

The query handles PAGING on the website, so everytime i need to get the total count of rows based on the search parameters, and then i have to select the rows in the current PageIndex (based on specified pageSize).

To get the total rows in the first query select them into a variable, like this:

@totalPropertiesCount = COUNT(id)

First of, i have a problem with getting the total COUNT of rows when using the following (in query number 2):

SELECT TOP 200
ROW_NUMBER() OVER (ORDER BY id ASC) AS row...

So for the time being i need select the content TWICE, once to get the total count, and then again to get rows based on the current pageIndex (so obviously the query needs some tuning).

Let me know if you want me to paste the whole query into the thread.

Thanks and regards,
Bjorn.

Have you checked out these liks?

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx -- SQL Server 2005
http://www.4guysfromrolla.com/webtech/041206-1.shtml -- SQL Server 2000
http://www.4guysfromrolla.com/webtech/042606-1.shtml -- SQL Server 2000

Gaurav
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
http://blogs.msdn.com/gauravbi/default.aspx

Thanks a lot for the quick reply guys!
Those links were a great help!

Bjorn.