SQL Server tutorial for beginners - 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 Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 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