SQL pagination: get fixed number of rows page by page

See the following examples:

SELECT * FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable) As Tmp
WHERE SortNum Between 11 AND 20

or

WITH Tmp As
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable)
SELECT * FROM Tmp
WHERE SortNum Between @StartRow AND @StartRow+@NumberOfRows-1


or

WITH Tmp As
SELECT ROW_NUMBER() OVER (ORDER BY Id) AS [SortNum], * FROM MyTable)
SELECT * FROM Tmp
WHERE SortNum Between (@page-1)*10+1 AND (@page)*10


The first one gives the second page of 10 records.
The second example shows the usage of WITH keyword and getting rows passed as parameter of stored procedure.
The third one shows you how to use 1 passed parameter as number of the desired page.