SQL Server Denali makes it much easier to do paging compared to previous versions of SQL Server. In SQL Server 2005 or 2008 you would do something like the following to skip the first 50 rows

WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY number) AS ROW,* 
FROM master..spt_values
WHERE TYPE ='P'
) 

SELECT * FROM cte
WHERE ROW BETWEEN 51 AND 100

Take a look at how you do this in SQL Server Denali, it is much easier and cleaner in my opinion

SELECT * 
FROM master..spt_values
WHERE type ='P'
ORDER BY number
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;

This syntax also works with parameters, here is the same code using a parameter for both the SQL Server Denali and ²⁰⁰⁵⁄₂₀₀₈ version.

DECLARE @i int = 51;

WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY number) AS ROW,* 
FROM master..spt_values
WHERE TYPE ='P'
) 

SELECT * FROM cte
WHERE ROW BETWEEN @i AND (@i * 2) -1

DECLARE @i int = 50

SELECT * 
FROM master..spt_values
WHERE type ='P'
ORDER BY number
OFFSET @i ROWS FETCH NEXT  @i ROWS ONLY;

If you want all rows after skipping the first 50 rows, you would do something like this in a version pre SQL Server Denali

DECLARE @i int = 51;

WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY number) AS ROW,* 
FROM master..spt_values
WHERE TYPE ='P'
) 

SELECT * FROM cte
WHERE ROW >= @i 

In SQL Server Denali it becomes very simple, all you need to do is OFFSET N ROWS

DECLARE @i int = 50

SELECT * 
FROM master..spt_values
WHERE type ='P'
ORDER BY number
OFFSET @i ROWS;

So that was just a quick look at OFFSET.

Click on the SQL Server Denali tag to see all our SQL Server Denali related posts