How to make SELECT LIMIT (from, to) in MSSQL 2005

How to make SELECT LIMIT (from, to) in MSSQL 2005

Lots of developers complain, that Microsoft SQL hasn't something similar to LIMIT(from, to) as MySQL has. In past time there was a solution by SELECT TOP n and inserted SELECT TOP n- With new T-SQL commands in Miscrosoft SQL Server 2005 is situation simpler.

DECLARE @rowsperpage INT
DECLARE @start INT

SET @start = 10002
SET @rowsperpage = 5

SELECT * 
FROM (
 SELECT row_number() 
 OVER (ORDER BY id desc) AS rownum, *
 FROM   my_member
) AS A
WHERE A.rownum BETWEEN (@start) AND (@start + @rowsperpage - 1)
go

Another example:

DECLARE @rowsperpage INT
DECLARE @start INT

SET @start = 10002
SET @rowsperpage = 5

SELECT * 
FROM (
 SELECT ROW_NUMBER() 
 OVER (ORDER BY id DESC) AS Row, * 
 FROM my_member
) AS LogWithRowNumbers 
WHERE Row >= @start AND Row <=(@start + @rowsperpage - 1)