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

T-SQL
1
2
3
4
5
6
7
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
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

T-SQL
1
2
3
4
5
SELECT * 
FROM master..spt_values
WHERE type ='P'
ORDER BY number
OFFSET 50 ROWS FETCH NEXT 50 ROWS ONLY;
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 2005/2008 version.

T-SQL
1
2
3
4
5
6
7
8
9
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 = 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
T-SQL
1
2
3
4
5
6
7
DECLARE @i int = 50
 
SELECT * 
FROM master..spt_values
WHERE type ='P'
ORDER BY number
OFFSET @i ROWS FETCH NEXT  @i ROWS ONLY;
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

T-SQL
1
2
3
4
5
6
7
8
9
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 
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

T-SQL
1
2
3
4
5
6
7
DECLARE @i int = 50
 
SELECT * 
FROM master..spt_values
WHERE type ='P'
ORDER BY number
OFFSET @i 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