How to select data top x data after y rows from SQL Server

Question

For example I have a table which contains 10'000 rows. I want to select top 100 rows after top 500th row. How can I do this most efficiently.

Query needed for SQL Server 2008

---

For example i have this query already but i wonder are there any more effective solution

SELECT TOP 100 xx
FROM nn 
WHERE cc NOT IN 
   (SELECT TOP 500 cc
      FROM nn ORDER BY cc ASC)

Answer 1

Tutorial 25: Efficiently Paging Through Large Amounts of Data

with cte as (
 SELECT ...,
  ROW_NUMBER () OVER (ORDER BY ...) as rn
 FROM ...)
SELECT ... FROM cte 
WHERE rn BETWEEN 500 and 600;