PAGINATION is the concept of splitting a larger data set into several smaller sets before transferring and displaying to the user. What this means is that instead of sending large bulks of information across the wire, that would otherwise consume a lot of resources (network traffic, memory/cache etc), we split the large bulks into smaller manageable chunks and only handle as much data that is needed.

Pagination can be seen everywhere; from Blogs and Forums, to News sites. All these use some form of pagination or another. Without pagination Forums would be showing thousands of topics at once instead of showing 20 topics per page; Blogs would be showing hundreds of blog posts per page instead of 10 posts per page. News sites… well.. they would take a rather long time to load if they displayed all of articles at once!

There are several forms of Pagination available; and no doubt you would have come across one or more already:

  • The first is what I refer to as Preemptive Loading
  • The second is On Demand Loading

In this article I will explain each of these in detail. I will explain the short comings of Preemptive Loading, and the benefits of On Demand Loading; and also explain how On Demand Loading can be achieved via SQL Server…

Different Types of Pagination

Preemptive Loading:

The First, and easiest type of Pagination is Client side pagination. This is when all the data that ‘may be’ required to be displayed is transferred from the Server to the Client all one go; and then pagination is achieved on the Client side through local components (such as data grids, data pagers, or custom logic). I call it Preemptive Loading. The key point in preemptive loading is that all entities available to the paged component are loaded even before the data is visible to the user. Is this good or bad ?

There’s no distinctive answer because some times, preloading is amazing from a usability point of view. But preloading can also mean trouble when loading massive amounts of unnecessary data.

Preemptive Loading – When it’s Good:

Take an Image Gallery application for example. You click on a thumbnail to preview the full sized image; navigation options allow you to view Previous and Next images in the gallery. This is a great example where preloading the next image is great! When the user clicks next, the image is already preloaded for viewing immediately; and this would then trigger another preload for the next image in the list and so forth. From a usability point of view, the images appear right away instead of waiting for the system to transfer the file ‘after’ the user has clicked ‘next’.

Preemptive Loading – When it’s Bad:

Hypothetically, say that you have a Blog that stores 1mb of data for each page; and that the system has been built to preload a total of 5 pages (refer to the image below). A total of 5mb is transferred on initial load. What would happen if the user only wanted to view the 1st page. The system would have processed and transferred a total of 5mb when only 1mb of data was really needed by the user. Multiply this with 100 concurrent users, and you will being to see the impact this will have on the overall system performance.

Premptive Loading: Total = 5 pages. 1mb per page.

On Demand Loading:

The second type of Pagination is what I call On Demand Loading; and this is the opposite of Preemptive Loading. On Demand Loading refers to a ‘delayed’ loading of the data, and when loads it only loads enough data that has been requested by the user.

Take for example a Forum that holds a total of 5000 topics. The Forum has been Paginated to show the first 1000 topics (lets say 1000 topics = 1mb for example). With Preemptive Loading, 5 pages of 1000 topics each would have been transferred at once; a total of 5mb. On the other hand, with On Demand Loading, a mere 1 page with 1000 topics are transferred; saving the system 4mb of unnecessary bandwidth! In contrast to the above diagram, the diagram below depicts On Demand Loading with 1mb of data transfer per page, per request.

Examples like this is exactly where you should prefer a system with On Demand Loading over Preemptive Loading!

On Demand Loading: Total = 1 page. 1mb per page.

Is it really necessary ?

For small to medium sized applications On Demand Loading might not be necessary; and it could be more trouble than what it is worth. However On Demand Loading in the essence of pagination is critical to Enterprise development as a means of achieving high efficiency and performance output in the system.

So how do I get it done ?

Here I will show you how you can achieve On Demand Loading using SQL Server.

A) Start by creating a Product table in your database. You can use the following script to do so…:

CREATE TABLE [dbo].[Product](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[CurrentProduct] [bit] NOT NULL,
	[Quantity] [int] NOT NULL,
	[ExpiryDate] [datetime] NOT NULL,
	[Supplier] [nvarchar](50) NULL,
	[Ranking] [int] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

B) Script out a few hundred records and insert them into the Product table.

C) Once you’ve done that create a stored procedure with the following signature:

CREATE PROCEDURE [dbo].[uspProduct_GetAllProducts]
	@SearchString				AS VARCHAR(50)
	, @SortColumn				AS VARCHAR(100)
	, @SortAscending			AS BIT
	, @PageIndex				AS INT
	, @PageSize					AS INT
	, @TotalCount				AS INT OUTPUT
AS
BEGIN
END

Parameters:

  • @SearchString: A filtering parameter to limit our data (optional).
  • @SortColumn: The name of the data column for which our data set will be ordered by.
  • @SortAscending: A boolean value specifying whether to sort ascending or not.
  • @PageIndex: The page index of the data that we want returned.
  • @PageSize: The number of entities per page
  • @TotalCount: Output parameter that will hold the total number entities that matched the query

The Design

Before I dive into the body, let me give you the overall design for this stored procedure.

The concept behind SQL Pagination is as follows:

  1. Create temporary table to hold ALL the records matching your query. This table is to hold a column called RowNumber which will be unique per row.
  2. Insert ALL records that match your query into the temporary table. For the RowNumber column use the SQL ‘OVER‘ clause to sort and order the records
  3. Set the @TotalCount output parameter from the temporary table by using a simple SELECT COUNT(*)
  4. Paginate the data in your temporary table by using the RowNumber, @PageIndex and @PageSize values to return all records GREATER THAN [first entity] AND LESS THEN [last entity]

Now that we have the concept at hand, lets check out the code..

D) Declare your temporary table as follows. Note the RowNumber field in this temporary table that will store the sort order of the records.

	DECLARE @tmp AS TABLE (
			RowNumber				INT
			, ProductID				INT
			, Name					NVARCHAR(50)
			, Price					DECIMAL(18,2)
			, CurrentProduct		BIT
			, Quantity				INT
			, ExpiryDate			DATETIME
			, Supplier				NVARCHAR(50)
			, Ranking				INT
			)

Below is the main query. This should be changed to your own custom logic to filter out the records prior to pagination. For this example, we simply filter all products that match our @SearchString parameter.

			-- The main SQL query that returns ALL the valid data
			SELECT *
			FROM Product P
			WHERE P.Name like '%' + ISNULL(@SearchString, '') + '%'

Sorting using the SQL ‘OVER’ clause:

The SQL Server ‘OVER’ clause allows you to order your record set and generate row rankings across an ORDER BY subquery. What this means is that you have an ORDER BY clause that will sort based on the input parameters @SortColumn and @SortAscending. Then based on this ordering, your record set (ie your main query records) are then ordered by the ROW_NUMBER function based on the ORDER BY subquery. The end result is that your temporary table will be populated with RowNumbers 1 to xxx in the sort order specified by your @Sort Parameters.

E) Below is a snippet on how to number our records based on the ‘ID’ column:

	INSERT INTO @tmp
	SELECT ROW_NUMBER() OVER
	( ORDER BY
		CASE		-- Numeric ASC
			WHEN @SortColumn = 'ID' AND @SortAscending = 1 THEN Q.ID
			ELSE NULL
		END ASC
		, CASE		-- Numeric DESC
			WHEN @SortColumn = 'ID' AND @SortAscending = 0 THEN Q.ID
			ELSE NULL
		END DESC
	) AS RowNumber
	, Q.ID
	, Q.Name
	, Q.Price
	, Q.CurrentProduct
	, Q.Quantity
	, Q.ExpiryDate
	, Q.Supplier
	, Q.Ranking
	FROM (
			-- The main SQL query that returns ALL the valid data
			SELECT *
			FROM Product P
			WHERE P.Name like '%' + ISNULL(@SearchString, '') + '%'
	) AS Q

F) Once you have all the records in your temporary table. You can now set the @TotalCount output parameter as follows:

	SET @TotalCount = ISNULL((SELECT COUNT(RowNumber) FROM @tmp), 0)

G) Finally, you can now safely paginate your temporary table data and return the paginated data as per the @PageIndex and @PageSize values. There’s some small logic here to grab the correct minimum RowNumber and maximum RowNumber values.. but other than that, it should be straight forward:

NOTE: Passing in @PageIndex = -1, @PageSize = -1 will return ALL records from your query un-paginated. This can be changed as you see fit in the logic below. But I have added this logic incase it was needed.

	-- The final SQL query that returns:
	--		The PAGINATED data only, Sorted as specified
	SELECT *
	FROM @tmp T
	WHERE T.RowNumber > CASE WHEN ISNULL(@PageIndex, 0) * ISNULL(@PageSize, 0) <= 0 THEN 0
							ELSE ISNULL((@PageIndex)*@PageSize, 0)
						END
		AND T.RowNumber <= CASE WHEN ISNULL(@PageIndex+1, 0) * ISNULL(@PageSize, 0) <= 0 THEN @TotalCount
								ELSE ISNULL((@PageIndex+1)*@PageSize, 100)
							END

Testing it all out

Once that’s done, that’s it! You can now run your stored procedure to see the results

DECLARE	@TotalCount int

EXEC	[dbo].[uspProduct_GetAllProducts]
		@SearchString = N'a',
		@SortColumn = N'ID',
		@SortAscending = 1,
		@PageIndex = 0,
		@PageSize = 10,
		@TotalCount = @TotalCount OUTPUT

SELECT	@TotalCount as N'@TotalCount'

Summary

Hopefully everything worked out fine! Congratulations, you have created a procedure that returns paginated data with Sorting integrated into its logic.

NOTE: The Sorting can be extended to all the fields you require. In the snippet below I have included the entire Stored Procedure logic that allows sorting on all columns of the Product table.

CREATE PROCEDURE [dbo].[uspProduct_GetAllProducts]
	@SearchString				AS VARCHAR(50)
	, @SortColumn				AS VARCHAR(100)
	, @SortAscending			AS BIT
	, @PageIndex				AS INT
	, @PageSize					AS INT
	, @TotalCount				AS INT OUTPUT
AS
BEGIN
	DECLARE @tmp AS TABLE (
			RowNumber				INT
			, ProductID				INT
			, Name					NVARCHAR(50)
			, Price					DECIMAL(18,2)
			, CurrentProduct		BIT
			, Quantity				INT
			, ExpiryDate			DATETIME
			, Supplier				NVARCHAR(50)
			, Ranking				INT
			)
	INSERT INTO @tmp
	SELECT ROW_NUMBER() OVER
	( ORDER BY
		CASE		-- Numeric ASC
			WHEN @SortColumn = 'ID' AND @SortAscending = 1 THEN Q.ID
			WHEN @SortColumn = 'Quantity' AND @SortAscending = 1 THEN Q.Quantity
			WHEN @SortColumn = 'Price' AND @SortAscending = 1 THEN Q.Price
			ELSE NULL
		END ASC
		, CASE		-- Numeric DESC
			WHEN @SortColumn = 'ID' AND @SortAscending = 0 THEN Q.ID
			WHEN @SortColumn = 'Quantity' AND @SortAscending = 0 THEN Q.Quantity
			WHEN @SortColumn = 'Price' AND @SortAscending = 0 THEN Q.Price
			ELSE NULL
		END DESC
		, CASE		-- Text	ASC
			WHEN @SortColumn = 'Name' AND @SortAscending = 1 THEN Q.Name
			WHEN @SortColumn = 'Supplier' AND @SortAscending = 1 THEN Q.Supplier
			ELSE NULL
		END ASC
		, CASE		-- Text DESC
			WHEN @SortColumn = 'Name' AND @SortAscending = 0 THEN Q.Name
			WHEN @SortColumn = 'Supplier' AND @SortAscending = 0 THEN Q.Supplier
			ELSE NULL
		END DESC
		, CASE		-- Date ASC
			WHEN @SortColumn = 'ExpiryDate' AND @SortAscending = 1 THEN Q.ExpiryDate
			ELSE NULL
		END ASC
		, CASE		-- Date DESC
			WHEN @SortColumn = 'ExpiryDate' AND @SortAscending = 0 THEN Q.ExpiryDate
			ELSE NULL
		END DESC
	) AS RowNumber
	, Q.ID
	, Q.Name
	, Q.Price
	, Q.CurrentProduct
	, Q.Quantity
	, Q.ExpiryDate
	, Q.Supplier
	, Q.Ranking
	FROM (
			-- The main SQL query that returns ALL the valid data
			SELECT *
			FROM Product P
			WHERE P.Name like '%' + ISNULL(@SearchString, '') + '%'
	) AS Q

	SET @TotalCount = ISNULL((SELECT COUNT(RowNumber) FROM @tmp), 0)
	-- The final SQL query that returns:
	--		The PAGINATED data only, Sorted as specified
	SELECT *
	FROM @tmp T
	WHERE T.RowNumber > CASE WHEN ISNULL(@PageIndex, 0) * ISNULL(@PageSize, 0) <= 0 THEN 0
							ELSE ISNULL((@PageIndex)*@PageSize, 0)
						END
		AND T.RowNumber <= CASE WHEN ISNULL(@PageIndex+1, 0) * ISNULL(@PageSize, 0) <= 0 THEN @TotalCount
								ELSE ISNULL((@PageIndex+1)*@PageSize, 100)
							END
END

Conclusion

Hope this article has helped explain the different types of Pagination. Both Preemptive Loading and On Demand Loading has its uses; and you will need to make a judgment call on which is best suited to your application design. Please take into consideration the performance impact that both types of Pagination could have in your environment.

Have fun! :-)