The one issue that dogs most programmers is application performance and there are certain things that are in our control always, one of that being returning only the necessary data from the backend. This is very relevant for web applications. Some form of grid is almost used in every web application and we implement paging, let it be client side or server side. Most of these are done in the client end using jQuery or in the application memory using code behind. We use true database pagination very rarely where we fetch only the records that are shown currently in the grid. Even when we do it has been complicated depending upon Row_Number and other complicated methods.SQL Server 2012 makes this much easier with the introduction of OFFSET and FETCH NEXT. We will learn about these 2 in this tip. These are attributes used with the ORDER BY clause.
Books online defines the syntax as:
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
Specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.
offset_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns defined in the outer query scope. That is, it cannot be
correlated with the outer query.
ROW and ROWS are synonyms and are provided for ANSI compatibility.
In query execution plans, the offset row count value is displayed in the Offsetattribute of the TOP query operator.
FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
Specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.
fetch_row_count_expression can be a variable, parameter, or constant scalar subquery. When a subquery is used, it cannot reference any columns defined in the outer query scope. That is, it cannot be correlated with the outer query.
FIRST and NEXT are synonyms and are provided for ANSI compatibility.
ROW and ROWS are synonyms and are provided for ANSI compatibility.
In query execution plans, the offset row count value is displayed in the Rows or Topattribute of the TOP query operator.