Row Offset in SQL Server

OFFSET FETCH Clause used for pagination.

Step 1:- Create table

CREATE TABLE #Test

(

       SrNo int identity,

       Column1 varchar(50),

       Column2 varchar(50)

)

Step 2:- Insert Record in the table

INSERT INTO #TEST(COLUMN1,COLUMN2)VALUES((RAND() * 100),NEWID())

GO 200

Step 3:- Select the table, you will get the 200 records set.

SELECT * FROM #TEST

Step 4:- Create the store procedure

CREATE PROC spGetRecord

@PAGENO INT = 1,

@PAGESIZE INT = 10

AS

BEGIN

       SELECT *

       FROM #TEST

       WHERE SRNO BETWEEN (@PAGENO*@PAGESIZE)-@PAGESIZE+1 AND (@PAGENO*@PAGESIZE)

       -- With ROW_NUMBER()

       SELECT *

       FROM (

                     SELECT *, ROW_NUMBER() OVER (ORDER BY SRNO) AS ROWNUM

                     FROM #Test

               )  AS Test

       WHERE ROWNUM BETWEEN (@PAGENO*@PAGESIZE)-@PAGESIZE+1 AND (@PAGENO*@PAGESIZE)

       -- SQL Server 2008+

       -- With OFFSET-FETCH

       SELECT *

       FROM #TEST

       ORDER BY SRNO ASC

       OFFSET (@PAGENO*@PAGESIZE)-@PAGESIZE ROWS

       FETCH NEXT @PAGESIZE ROWS ONLY

END

Step 4:- Pass the page number to get the page record.

EXEC spGetRecord 1

Explanation:-

Step 1:-

1. 1.  If you skip the FETCH, The result set Skip first 195 rows from the sorted result set and return the remaining rows.
2. Ex.

       SELECT *

       FROM #TEST

       ORDER BY SRNO ASC

       OFFSET 195 ROWS

Step 2:-

1. 1.  If you assign OFFSET value 190 and FETCH NEXT value 10, the result set Skip first 190 rows from the sorted result set and return next 10 rows.
2. Ex.

       SELECT *

       FROM #TEST

       ORDER BY SRNO ASC

       OFFSET 190 ROWS

       FETCH NEXT 10 ROWS ONLY

Note:-

1. 1.  OFFSET-FETCH can be used only with the ORDER BY clause.
2. 2.  A TOP cannot be used in the same query or sub-query as an OFFSET.
3. Ex.

       SELECT top 5 *

       FROM #TEST

       ORDER BY SRNO ASC

       OFFSET 190 ROWS

       FETCH NEXT 10 ROWS ONLY

1. 3.  The offset specified in an OFFSET clause may not be negative.
2. Ex.

       SELECT *

       FROM #TEST

       ORDER BY SRNO ASC

       OFFSET -190 ROWS

       FETCH NEXT 10 ROWS ONLY

1. 4.  The number of rows provided for a FETCH clause must be greater than zero.
2. Ex.

       SELECT *

       FROM #TEST

       ORDER BY SRNO ASC

       OFFSET 190 ROWS

       FETCH NEXT 0 ROWS ONLY