How can I make MySQL's LIMIT in SQL Server? [duplicate]

This question already has answers here: Paging SQL Server 2005 Results (6 answers) Closed 8 years ago.

I'm retrieving thousands data from database. I would like to show on web pages by limit of 100 records. I can show First 100 record with the following SQL:

SELECT TOP 100 * FROM TBLStock

And How can I navigate next r开发者_如何学Pythonecords 101 to 200, 201 to 300, etc.. ? Please Help me. I am using SQL Server 2005. HELP! How can I write in SQL Server like LIMIT in MySQL?

---

Method 1: SELECT TOP 100 FROM (SELECT TOP 500 ORDER BY )

AFAIK this is the only way before Yukon (MS SQL server 2005). Double select with reverse ordering in the middle.

Surprisingly, it is also said to be efficient.

Method 2: Use Row_Number() function that is available starting SQL Server 2005, as the other post suggests.

---

SELECT t.*
FROM
(
    SELECT first_column, second_column, third_column, etc,
        ROW_NUMBER() OVER (ORDER BY sort_column) AS row_num
    FROM your_table
) AS t
WHERE t.row_num BETWEEN 50 AND 100
ORDER BY t.row_num