How to do paging in MS SQL query?

Question

What is the easiest way for paging in MS SQL? I have tried nested queries where I select TOP results and then reverse order and select TOP in the result again. But is there any way like LIMIT in MySQL?

Answer 1

Simple example:

DECLARE @OffsetRows tinyint = 0
, @FetchRows tinyint = 20;

SELECT Id, Data, Date
FROM MyTable
ORDER BY Date
OFFSET @OffsetRows ROWS
FETCH NEXT @FetchRows ROWS ONLY;

Answer 2

Please try OFFSET FETCH clause of MS SQL Server 2012 . See link http://technet.microsoft.com/en-us/library/gg699618.aspx