XML FeedsSQL 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
tsql
- 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
tsql
- 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.
tsql
- 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
tsql
- 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
tsql
- 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
tsql
- 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
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
It's cleaner syntax that's for sure. Does it have nicer execution plans? 
I will modify and post the execution plans tonight 
MYSQL alreasy has these features since 2004. Thanks to Microsoft...atleast they introduced them in 2011.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.