Paging - MS SQL

[royshoa]

Hello,
I have a big web-site(ASP.NET) with a huge data-base(MS-SQL) and i searched for a good way to use paging in my web-site.
I found two good way's to do it but i have a little problem to decide which paging way to use,
The first way to is to use ROW_NUMBER() in this why:

With ListRec As(
SELECT ROW_NUMBER() OVER(ORDER BY field1,field2) AS rownum,field1,field2,field3 FROM [MyTable] WHERE MyCondition
)
SELECT *
FROM ListRec
WHERE rownum > 90 and rownum <= 100

The second way to is to use NOT IN in this why:

SELECT TOP 10 field1,field2,field3 FROM [MyTable]
WHERE (field1 NOT IN (SELECT TOP 90 field1 FROM [MyTable] WHERE MyCondition ORDER BY field1,field2)) And MyCondition ORDER BY field1,field2

So, Which way is the best way ?

Thanks,
Roy Shoa.

[sknake]

That is a tough one. Run the query both ways and take a look at the execution plan and see what indicators it gives you. You can include the actual and estimated plan using SQL Server Management Studio.

[royshoa]

That is a tough one. Run the query both ways and take a look at the execution plan and see what indicators it gives you. You can include the actual and estimated plan using SQL Server Management Studio.

Thanks,
I found the way.
The best way is:

SELECT * FROM (SELECT TOP(X) * FROM (SELECT * TOP(Y) FROM MyTable As T1 WHERE myCondition Order by My Order BY) As T2) 
AS T3.

I test it over 7,000,000 records and it is the best one.

[sknake]

Thank you for reporting back your results. Out of curiosity -- What did you use to determine which method was best?

Please mark this thread as solved (since you answered your own question :P) and good luck!