Pagination in SQL Server 

Hey, you have looked around google to get here - here is the pagination query in SQL Server solution. In this tutorial, let see what is pagination and how to implement in a single SQL query.

What is Pagination?

A simple process of dividing by segment from a large data source. The process helps user to have required data over larger data and not letting user tired of wholesome. 

In a real time world example, someone gives you a box with 100 coins and asked to reconfirm that count is correct or not - what would you do first?  hey, try not to ask what's the point to recount when the count is already known - 'great minds think a like'.

Let's do the segments,

Step 1:  Open the box and take out only 10 coins first 

Step 2:  Continue to take out another 10 coins, and each step pick only 10 coins 

.

.

.

.

.

Step 10: Finish line and you know the remaining 10 coins left.

So, What's next? 

Now, after 10 segments done easy to explain the person and hand-over segments. Isn't easy for anyone having 100 coins trying to look at each one than by 10 segments? Yes-quite light weight and manageable easily.

How this 100 coins example relates to Pagination in SQL Server?

Good, you asked the right question - how is it like you imagine 100 coins similar to 100 records in a table?  your project requirement is to pull 100 records for user to view. Imagine every time you make connection to SQL Server just to read hundred rows-isn't heavy on process load? Is it efficient way to fetch data from SQL Server?

So, saying all these, how do I proceed to fetch data effectively.  Data Paging implemented in web application.

What to do next? 

Your project requirement is to pull all the records of a customer and display in customer list on web page or windows application datagridview. You realize the data in customer table is huge and you would like to implement pagination on SQL Server data fetch.

How SQL Server implements pagination?

In SQL Server query uses few keywords like OFFSET, FETCH, ORDER BY in a select query statement

OFFSET -  This specifies the number of rows to skip before it starts to return rows from the query expression. The value can be an integer constant or expression that is greater than or equal to zero.

FETCH -   This specifies the number of rows to return after the OFFSET clause has been processed. The value can be an integer constant or expression that is greater than or equal to one.

ORDER BY - This is when ORDER BY clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses.

Top 2 things to highlight;

• Include page number and how many records you would like to fetch
• Include ORDER BY column name in query. 

  

Here you go with all the Implementation of pagination on SQL Server Query and Stored Procedure 

Skip the first 3 rows and return the rest of the rows

// Skip the first 3 rows and return the rest.

SELECT ID, FirstName,LastName,Age,Location,Active FROM dbo.Customers ORDER BY ID OFFSET 3 ROWS;

Skip 0 row and return the next 10 rows only

// Skip 0 row and return 10 rows.

SELECT ID, FirstName,LastName,Age,Location,Active FROM dbo.Customers ORDER BY ID OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

Final, Implementation of Pagination on SQL Server  - Stored Procedure

// @PageNumber The current page being displayed.

DECLARE @PageNumber AS INT

// @RowsOfPage The number of rows in a page.

DECLARE @RowsOfPage AS INT

SET @PageNumber = 1

SET @RowsOfPage = 10

// Customers is the SQL table name you are fetch all rows.

SELECT * FROM Customers

ORDER BY CustomerID

OFFSET (@PageNumber-1)*@RowsOfPage ROWS

FETCH NEXT @RowsOfPage ROWS ONLY

Result : 

When you execute the above SQL statement the pagination results as  first image, the second shows there are 12 rows in customers table - since pagination applied maximum rows to 10. 

 

Limitations and Restrictions on keywords? 

The ORDER BY clause is not valid in views, inline functions, derived tables, and sub queries, unless either the TOP or OFFSET and FETCH clauses are also specified. 

When ORDER BY is used in these objects, the clause is used only to determine the rows returned by the TOP clause or OFFSET and FETCH clauses. 

The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

OFFSET and FETCH are not supported in indexed views or in a view that is defined by using the CHECK OPTION clause. 

Microsoft recommends that you use the OFFSET and FETCH clauses instead of the TOP clause to implement a query paging solution and limit the number of rows sent to a client application.

Using OFFSET and FETCH as a paging solution requires running the query one time for each "page" of data returned to the client application. This is how Paging applied in C# programming

For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state.

Summary 

So far you have seen and learned step by step how to implement pagination on SQL Server, prepare to apply in your project. In fact- adding query logic in stored procedure will yield good security measure and performance level up.