SQL Server Denali is coming up with new T-SQL of Paging. I have written about the same earlier.
SQL SERVER – Server Side Paging in SQL Server Denali – A Better Alternative
SQL SERVER – Server Side Paging in SQL Server Denali Performance Comparison
SQL SERVER – Server Side Paging in SQL Server Denali – Part2
What is very interesting is that SQL Server CE 4.0 have the same feature introduced. Here is the quick example of the same. To run the script in the example, you will have to do install Webmatrix 4.0 and download sample database. Once done you can run following script.
FROM [Order Details] ORDER BY [Order ID] OFFSET 20 ROWS
FETCH NEXT 7 ROWS ONLY
Above script returns records 21 to 27 from the tables [Order Details]. OFFSET clause will skip the first 20 rows and then returns 7 rows. Please note, this T-SQL will throw error if ORDER BY is not used in the query.
I have found exactly similar post written by Jacob Sebastian on BeyondRelational.
Reference: Pinal Dave (http://blog.SQLAuthority.com)