SQL Server 2012 implements paging new syntax

Recently have been looking at SQL Server books, but look at the basic query flow, query in the work of the most, so it is important to correctly query the desired data.

See in the book in SQL Server 2012 a new implementation of paging Query method, feel more convenient than the original, so record, if can help everyone, is very happy ~

Before paging, I basically use the row_number () function, and in the where statement with between and to implement, each time must write subquery, feel very troublesome, example:

SELECT * from (    SELECTID, row_number () Over(ORDER  by [Datetime] DESC) asNum fromdbo.tbluser) asTWHERET.numbetween  One  and  -

In SQL Server 2012, you do not need to write subqueries, directly in the order BY statement can be implemented pagination! Cases:

 select   ID, row_number ()  over  (order  by  [ datetime  ]  desc ) as   num  from   Dbo.tbluser  order  by   Numoffset  10   ROWS  fetch  next  10  ROWS only 

Note that the syntax is to introduce two new parameter OFFSET and FETCH in the ORDER BY clause, so they must be used only in the order BY statement and must be used in conjunction to make paging work.

OFFSET specifies the number of rows to skip before returning the query results, and the number of rows returned after the fetch specifies offset.

SQL Server 2012 implements paging new syntax