Offset fetch next in SQL Server 2012

sql server 2012 offset fetch next
sql server 2012 paging stored procedure
sql server offset fetch example
mssql offset fetch

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.

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

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

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/offset-fetch-next-in-sql-server-2012.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/10/offset-fetch-next-in-sql-server-2012_22.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists