Paging With SQL Server Stored Procedures In ASP.NET

Data paging is very useful when you work with large amount of data. Instead of confusing your user with thousands or maybe even millions of records you simply show only first page with 10, 20 or 30 records and enable navigation buttons like Next or Previous if user wants to see other pages. Standard ASP.NET server controls, like GridView have built in paging capabilities in two modes: simple or custom.

Simple paging is easy to implement but it always bind complete records set which is not so scalable solution and can take a lot of network traffic and work very slow if you have large table or your web site is on shared hosting or especially if you have a lot of concurrent visitors. More about how to implement simple or custom paging in GridView and ListView you can read in Data Paging in ASP.NET tutorial.

But, which ever server control you use for data presentation, to get efficient and scalable data paging you need to do all your paging logic on SQL Server's side and SQL Server should return only selected page to ASP.NET web application. With logic like this, you get faster solution, need less memory and avoid too much traffic between SQL Server and web server. On SQL Server side, you can do paging on two main ways:

1. By building a dynamic SQL query

2. By using a stored procedure

Both methods are used widely, to find out how to build dynamic SQL queries for paging check SQL Queries For Paging In ASP.NET. In this tutorial we will see some common solution when paging is done with stored procedure.

Paging stored procedure with three nested queries

I will publish next time.