Well, I think we as a developer implemented a pagination in many application. But implement pagination with millions of records by keeping performance in mind always be a headache for developers. So we always tend to look for better approach and technique. From SQL Server 2012 sql server team at Microsoft comes with a lot of t-sql enhancement and OFFSET…FETCH NEXT clause is one of them which is used to implement pagination at sql server side in a very simple and elegant way. This feature is also appears to be less expensinve from the performance point of view than the previous technique we were using. We have recently upgraded one of our application database from SQL Server 2008 R2 to SQL Server 2014. Before SQL Server 2014, we were using Derived table with ROW_NUMBER() Function. In this post I’ll show you both the technique.
First let’s take a look at OFFSET…FETCH NEXT clause in action.
Let’s create a table with 3 millions of records.
SET NOCOUNT ON; CREATE TABLE Customer ( [CustomerID] [int] IDENTITY(1,1) NOT NULL, FirstName AS ('First' + CAST(CustomerID AS VARCHAR)), LastName AS ('Last' + CAST(CustomerID AS VARCHAR)), MobileNo VARCHAR(20), LandLineNo VARCHAR(20), City VARCHAR(20), State VARCHAR(20), Zip VARCHAR(20) ) INSERT INTO Customer VALUES('7625045789', '261-754-28354', 'NW', 'NW', '6592626') GO 3000000
Below syntax I have refer it from MSDN.
Now, Let’s understand each part.
This clause is something like ready made option for paging. Very easy to implement and understand. See the below script where we see the records of page no 91 with page size of 15.
DECLARE @PageNo INT = 91, @RecordPerPage INT = 15 SELECT CustomerID ,FirstName ,LastName ,MobileNo ,LandLineNo ,City ,State ,Zip FROM Customer ORDER BY CustomerID OFFSET ((@PageNo - 1) * @RecordPerPage) ROWS FETCH NEXT @RecordPerPage ROWS ONLY
Though this clause have some syntactic limitation like –
- OFFSET clause is mandatory with FETCH. You cannot directly use ORDER BY…FETCH.
- You cannot be combined TOP clause with OFFSET and FETCH in the same query.
- To use OFFSET…FETCH NEXT clause ORDER BY is compulsory.
In SQL Server 2008, we have implemented same paging functionality using ROW_NUMBER() Function like this –
DECLARE @PageNo INT = 91, @RecordPerPage INT = 15, @FromRowNum INT, @ToRowNum INT SET @FromRowNum = ((@PageNo - 1) * @RecordPerPage + 1); SET @ToRowNum = @PageNo * @RecordPerPage; SELECT * FROM (SELECT CustomerID ,FirstName ,LastName ,MobileNo ,LandLineNo ,City ,State ,Zip ,RowNum = ROW_NUMBER() OVER (ORDER BY CustomerID) FROM Customer) result WHERE (result.RowNum >= @FromRowNum AND result.RowNum <= @ToRowNum)
All-right both the query return the same result. But how OFFSET…FETCH NEXT clause is less expensive than above script which uses ROW_NUMBER() Function. Let’s take a look at execution plan of both the query.
Please take a note that the execution plan shown in above image may differ as per the environment. I have tested it in my PC with 3 million records. So before applying it to your production server, please test it with more records.
That’s it guys. Hope you enjoyed this post. Thank you for your time.