SQL server Pagination

Hi Readers

This post is about getting sql server results in a paginated manner,

Lets see the requirement
Simply when some one is requesting for a list of data you dont want to retrieve all records from the database and show it to the end user, this will affect the performance of the application and the user will be getting thousands on rows in a single request, think of the network traffic that can make.
So the solution will be getting only a subset of data, that is by pages. Show 1st 10 records of data then show the next 10.

This is easy to do when it you need to show the data that is already ordered by the primary key. you can use it to get the set of records easily, but what if its a dataset that you are showing for a search result, then the order will not be there.

Let see a way to achieve this.
The datasource used for this example is from the Adventure works database.
 We are using the product table in production schema.

Lets take the first 10 rows
ProductID    Name
1        Adjustable Race
2        Bearing Ball
3        BB Ball Bearing
4        Headset Ball Bearings
316    Blade
317    LL Crankarm
318    ML Crankarm
319    HL Crankarm
320    Chainring Bolts
321    Chainring Nut

There are two ways to achieve this
1st way is to use the offset keyword (Available from SQL server 2012)
2nd way is to use the row number and get results

Method 1
Offset - Fetch Method Introduced with SQL Server 2012
This method used the new clause that is offset and fetch
Here we'll have to first use the order by to order the results and fetch the records we need.

SELECT  [ProductID]
      ,[Name]
FROM [AdventureWorks2012].[Production].[Product]
ORDER BY Name
OFFSET N ROWS
FETCH NEXT M ROWS ONLY

This code skips the first 'N' number of rows and returns the next 'M' number of rows.You can use this for pagination by setting the N to the count that you have already read and the items per page as M.

Method 2
Ranking the results
This method simply ranks the results and you can use the rank to get the result set you want using a where clause.

SELECT T.*
FROM
(
    SELECT *,ROW_NUMBER() OVER (ORDER BY NAME DESC) AS [rank]
    FROM [AdventureWorks2012].[Production].[Product]
) AS T
WHERE T.[rank]>((@PageNo*@Count)-@Count) and T.[rank]<(@PageNo*@Count)+1

Here the @PageNo is the number of the page you need details for and the @Count is the number of items per page.

These are simple techniques that i used in one of my projects.

Happy Coding :)
-Guruparan-