Paging in SQL server 2012 stored procedure using OFFSET and FETCH

In this article we will learn about pagination using OFFSET and FETCH, OFFSET and FETCH is the new features of SQL Server 2012. In earlier version this feature is not available so was very difficult to implement paging in our procedures it also kill procedures performance.

Pagination is very easy in SQL Server 2012. New feature of SQL Server 2012(OFFSET and FETCH) is described below.

OFFSET: OFFSET skips the specific number of rows. The number can be zero or greater than zero.

FETCH: FETCH returns specific number of rows after processing the OFFSET clause. The number can be one or greater than one.

OFSET Syntax:

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

FETCH Syntax:

FETCH { FIRST|NEXT } <rowcount expression> { ROW|ROWS } ONLY

Limitation of OFFSET and FETCH:

1. If you are using OFFSET and FETCH, it’s mandatory to use ORDER BY clause.
2. OFFSET is mandatory if you are using FETCH.
3. In query expression you can’t use TOP with OFFSET and FETCH.
4. You can’t use COUNT(*) in query expression with OFFSET and FETCH.

Example: Suppose we have a table “Inventory” which has some basic data and we want to apply pagination when fetching the records of inventory in our procedure.

Run the following query to create “Inventory” table with some dummy records.

CREATE TABLE Inventory(        ID BIGINT IDENTITY(1,1) PRIMARY KEY        ,Title VARCHAR(500)        ,SKU VARCHAR(20)        ,Price DECIMAL(18,2)        ,Created_Date DATETIME        ,Updated_Date DATETIME ) DECLARE @cnt INT=1000 WHILE(@cnt>0) BEGIN        INSERT INTO Inventory(Title,SKU,Price,Created_Date,Updated_Date)        SELECT 'Title'+CONVERT(VARCHAR(5),@cnt),'SKU'++CONVERT(VARCHAR(5),@cnt),        10+@cnt,GETDATE(),GETDATE()        SET @cnt=@cnt-1 END

Now see the following script which is implemented with pagination.

-- ============================================= -- Author:                     Dilip Kumar SIngh -- Create date: 04,Aug 2016 -- Description:  -- ============================================= CREATE PROCEDURE usp_GetInventory             @Index INT=0,             @PageSize INT= 20 AS BEGIN              SET NOCOUNT ON;             SELECT ID,Title,SKU,Price,Created_Date             FROM Inventory ORDER BY ID ASC             OFFSET (@PageSize)*(@Index) ROWS --Offset to skip specific number of rows             FETCH NEXT @PageSize ROWS ONLY   --fetch to fetch specific number of rows             SET NOCOUNT OFF; END GO

Now execute the procedure to passing variable @Index and @Page it will fetch records accordingly.

Suppose I want to get 10 records for page index 1 then I will execute procedure as below.

EXEC usp_GetInventory @index=0,@PageSize=10

Result:

Note: in @Index you will pass the grid index value which should be started from zero, so you will pass value like @index=grid_ index-1, if index value is 1, 2,3,4,5 … or if you want to pass index value as same from 1 then change the procedure for OFFSET as OFFSET @PageSize*(@Index-1). In @pageSize you will pass the value to display number of records in grid.