Wednesday 9 April 2014

OFFSET FETCH in SQL Server



If you are given task to do custom paging then what you will do ?  you will write where condition and use row_number() function. But here SQL provide new way to get records as per our requirement for paging. Just write below query and pass start Record number and total record to get and you will gget your result :

SELECT * FROM  Table1
ORDER BY 1
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY


Arguments Explanation :


OFFSET : Specifies the number of rows to skip, before starting to return rows from the query expression. The argument for the OFFSET clause can be an integer or expression that is greater than or equal to zero. You can use ROW and ROWS interchangeably.


FETCH (FIRST|NEXT): Specifies the number of rows to return, after processing the OFFSET clause. The argument for the FETCH clause can be an integer or expression that is greater than or equal to one. You can use ROW and ROWS interchangeably. Similarly, FIRST and NEXT can be used interchangeably



Why Should be a fan as you are star of tomorrow....

1 comment:

  1. Nice Article !
    This is my pleasure to read your article.
    Really this will help to people of SQL Server Community.

    I have also prepared one article about, SQL Server 2012 custom paging using OFFSET and FETCH NEXT.
    You can also visit my article, your comments and reviews are most welcome.
    http://www.dbrnd.com/2016/01/sql-server-2012-custom-paging-using-offset-and-fetch-next/

    ReplyDelete