SQL: Page data using Row_Number function

Question:

How to page through data on SqlServer 2005 (or later)?
Page through data on SqlServer 2005 using ROW_NUMBERfunction?

Answer:

Here is an example

USE AdventureWorks
GO

WITH result_set AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY OrderQty DESC) AS [row_number], *
  FROM
    Sales.SalesOrderDetail
  WHERE
    1=1
) 
SELECT
  *
FROM
  [result_set]
WHERE
  [row_number] BETWEEN 50 AND 99