Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Offset fetch next in SQL Server 2012

Suggested Videos
Part 127 - Difference between DateTime and SmallDateTime in SQL Server
Part 128 - DateTime2FromParts function in SQL Server 2012
Part 129 - Difference between DateTime and DateTime2 in SQL Server



In this video 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

2 comments:

  1. thanks sir for the great effort,sir is there any to get the single row on top apart from that row other row should be at their own position.ex
    5
    1
    2
    3
    4
    6
    7
    8
    9
    10

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.