Offset and Fetch Next Keywords in SQL Server 2012

In this article I will explain the use of the new keywords OFFSET and FETCH NEXT in SQL Server 2012. In the earlier versions of SQL Server, if you use a GridView then you set its pagination property. However if you want to do this from the server side then you need to use the row_number() function and supply the specific range of rows and also retrieve the current page data from the database using a temp table. The ORDER BY OFFSET and FETCH NEXT ONLY keywords are one of the major features introduced in SQL Server 2012. There are new clauses named OFFSET and FETCH that can do pagination in SQL Server 2012. So let's take a look at a practical example. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

Creating a Table in SQL Server

Create TABLE UserDetail

(

       User_Id int NOT NULL IDENTITY(1,1),     

       FirstName varchar(20),

       LastName varchar(40) NOT NULL,

       Address varchar(255),     

       PRIMARY KEY (User_Id)

)

 

INSERT INTO UserDetail(FirstName, LastName, Address)

VALUES ('Smith', 'Kumar','Capetown'),

    ('Crown', 'sharma','Sydney'),

    ('Copper', 'verma','Jamaica'),

    ('lee', 'verma','Sydney'),

    ('Rajesh', 'Kumar','India'),

    ('Rahu', 'sharma','India'),

    ('Ravi', 'verma','Jamaica'),

    ('Leon', 'verma','Sydney'),

    ('Methews', 'Anglo','Srilanka'),

    ('Ben', 'lon','Newzealand'),

    ('Derrin', 'summy','Jamaica')

go

SELECT * FROM [master].[dbo].[UserDetail]

The table looks as in the following:

In SQL Server 2008

In SQL Server 2005/2008, we have been doing this data paging by writing a stored procedure or a complex query. Here is a sample of how we were using data paging in SQL Server 2005/2008 using the row_number function with an order by clause:

SELECT *

FROM (

SELECT ROW_NUMBER() OVER(ORDER BY User_Id) AS number, *

FROM userdetail) AS TempTable

WHERE number > 0 and number <= 4

The ROW_NUMBER function enumerates the rows in the sort order defined in the over clause.

Now using OFFSET and FETCH NEXT Keywords

OFFSET Keyword -  If we use offset with the order by clause then the query will skip the number of records we specified in OFFSET n Rows.

Select *

from userdetail

Order By User_Id

OFFSET 5 ROWS

In the preceding example, we used OFFSET 5 ROWS, so SQL will skip the first 5 records from the result and display the rest of all the records in the defined order. Now select the query and press F5 to execute the query:

OUTPUT

FETCH NEXT Keywords - When we use Fetch Next with an order by clause only, without Offset then SQL will generate an error.

SELECT *

FROM userdetail

ORDER BY User_Id

--OFFSET 5 ROWS

FETCH NEXT 10 ROWS ONLY;

 

Output

 

ORDER BY OFFSET and FETCH NEXT

In this example, query guides how to use both ORDER BY OFFSET and FETCH NEXT with a select statement for creating paging in SQL Server 2012.

 

SELECT *

FROM userdetail

ORDER BY User_Id

OFFSET 5 ROWS

FETCH NEXT 8 ROWS ONLY;

 

Output

 

 

Paging with stored procedure

 

In this example, creating a stored procedure with both ORDER BY OFFSET and FETCH NEXT keyword to enhancement the paging in SQL Server 2012.

 

CREATE PROCEDURE TestPaging

(

  @PageNumber INT,

  @PageSize INT

)

AS

DECLARE @OffsetCount INT

SET @OffsetCount = (@PageNumber-1)*@PageSize

SELECT  *

FROM [UserDetail]

ORDER BY [User_Id]

OFFSET @OffsetCount ROWS

FETCH NEXT @PageSize ROWS ONLY

 

Now execute the stored procedure and give the page number and page size to test paging. 

 

EXECUTE TestPaging 1,5

 

In the preceding query:

 

1: This is for the first page

5: Number of records to display on the page

 

Output

 

 

EXECUTE TestPaging 2,5

 

In the preceding query:

 

2: This is for the second page

5: Number of records to display on the page

 

Output