Offset fetch next in SQL Server 2012

we will discuss OFFSET FETCH Clause in SQL Server 2012

One of the common tasks for a SQL developer is to come up with a stored procedure that can return a page of results from the result set. With SQL Server 2012 OFFSET FETCH Clause it is very easy to implement paging. 

Let's understand this with an example. We will use the following tblProducts table for the examples in this video. The table has got 100 rows. In the image I have shown just 10 rows.



SQL Script to create tblProducts table

Create table tblProducts

(

    Id int primary key identity,

    Name nvarchar(25),

    [Description] nvarchar(50),

    Price int

)

Go

SQL Script to populate tblProducts table with 100 rows

Declare @Start int

Set @Start = 1

Declare @Name varchar(25)

Declare @Description varchar(50)

While(@Start <= 100)

Begin

    Set @Name = 'Product - ' + LTRIM(@Start)

    Set @Description = 'Product Description - ' + LTRIM(@Start)

    Insert into tblProducts values (@Name, @Description, @Start * 10)

    Set @Start = @Start + 1

End

OFFSET FETCH Clause

• Introduced in SQL Server 2012
• Returns a page of results from the result set
• ORDER BY clause is required

OFFSET FETCH Syntax : 

SELECT * FROM Table_Name

ORDER BY Column_List

OFFSET Rows_To_Skip ROWS

FETCH NEXT Rows_To_Fetch ROWS ONLY

The following SQL query
1. Sorts the table data by Id column
2. Skips the first 10 rows and
3. Fetches the next 10 rows

SELECT * FROM tblProducts

ORDER BY Id

OFFSET 10 ROWS

FETCH NEXT 10 ROWS ONLY

Result : 


From the front-end application, we would typically send the PAGE NUMBER and the PAGE SIZE to get a page of rows. The following stored procedure accepts PAGE NUMBER and the PAGE SIZE as parameters and returns the correct set of rows.

CREATE PROCEDURE spGetRowsByPageNumberAndSize

@PageNumber INT,

@PageSize INT

AS

BEGIN

    SELECT * FROM tblProducts

    ORDER BY Id

    OFFSET (@PageNumber - 1) * @PageSize ROWS

    FETCH NEXT @PageSize ROWS ONLY

END

With PageNumber = 3 and PageSize = 10, the stored procedure returns the correct set of rows

EXECUTE spGetRowsByPageNumberAndSize 3, 10