pagenite by rownumber, different of subquery vs with clause in mssql

This is one way (SQL2000)

SELECT * FROM
(
    SELECT TOP (@pageSize) * FROM
    (
        SELECT TOP (@pageNumber * @pageSize) *
        FROM tableName 
        ORDER BY columnName ASC
    ) AS t1 
    ORDER BY columnName DESC
) AS t2 
ORDER BY columnName ASC

and this is another way (SQL 2005)

;WITH results AS (
    SELECT 
        rowNo = ROW_NUMBER() OVER( ORDER BY columnName ASC )
        , *
    FROM tableName 
) 
SELECT * 
FROM results
WHERE rowNo between (@pageNumber-1)*@pageSize+1 and @pageNumber*@pageSize

and this is another way SQL2012

SELECT * FROM Table_1
ORDER BY id
OFFSET 1 ROWS FETCH NEXT 3 ROWS ONLY

example

SELECT * from (
SELECT *, ROW_NUMBER() OVER(ORDER BY id asc) as rownum FROM [test].[dbo].[Table_1]
) as a
where a.rownum > 1 and a.rownum < 4
GO
with b as (
SELECT *, ROW_NUMBER() OVER(ORDER BY (select 1)) as rownum FROM [test].[dbo].[Table_1]
)
select top 10 * from b where b.rownum > 1
GO