HI WELCOME TO SIRIS

Offset fetch next in SQL Server 2012

Leave a Comment

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.

Offset fetch next in SQL Server 2012

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 : 
sql server 2012 offset fetch next

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

sql server 2012 paging stored procedure

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.