Pagination is a common concept that allows users to view data in small subsets, similar to flipping through the pages of a book. While many development tools provide built-in pagination functionality, it is important to consider scalability and performance when implementing pagination in an application.

The biggest problem with built-in pagination is that it retrieves all the data from a subsequent query and performs filtering at the application tier, rather than the database tier. This can lead to greater overhead and slower load times, frustrating users who expect fast performance.

Fortunately, SQL Server 2005 introduced a new functionality called Row_Number, which makes custom pagination a breeze. Row_Number allows developers to assign a sequential number to each row returned, based on the specified partition and ordering. By returning only the necessary data to the application, we can improve performance and reduce stress on the server.

Let’s take a look at an example of how to implement custom pagination in SQL Server:

CREATE PROCEDURE usp_ContactPaging (
		@SortCol VARCHAR(25) = 'ContactId ASC',
		@FirstName VARCHAR(25) = NULL,
		@LastName VARCHAR(25) = NULL,
		@pgSize INT = 25,
		@pgNbr INT = 1
	)
	AS
	BEGIN
		DECLARE @NbrPages INT

		IF @FirstName IS NULL AND @LastName IS NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts
		END

		IF @FirstName IS NOT NULL AND @LastName IS NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName
		END

		IF @FirstName IS NULL AND @LastName IS NOT NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [LastName] = @LastName
		END

		IF @FirstName IS NOT NULL AND @LastName IS NOT NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName AND [LastName] = @LastName
		END

		;WITH PagingCTE (Row_ID, ContactId, FirstName, LastName)
		AS
		(
			SELECT ROW_NUMBER() OVER (
				ORDER BY
					CASE WHEN @SortCol = 'FirstName DESC' THEN FirstName END DESC,
					CASE WHEN @SortCol = 'FirstName ASC' THEN FirstName END ASC,
					CASE WHEN @SortCol = 'LastName ASC' THEN LastName END ASC,
					CASE WHEN @SortCol = 'LastName DESC' THEN LastName END DESC,
					CASE WHEN @SortCol = 'ContactID ASC' THEN ContactId END ASC,
					CASE WHEN @SortCol = 'ContactID DESC' THEN ContactId END DESC
			) AS [Row_ID],
			ContactId,
			FirstName,
			LastName
			FROM Contacts
			WHERE [FirstName] = COALESCE(@FirstName, FirstName)
			AND [LastName] = COALESCE(@LastName, LastName)
		)

		SELECT
			Row_ID,
			ContactId,
			FirstName,
			LastName,
			@pgNbr AS PageNumber,
			@NbrPages AS TotalNbrPages
		FROM PagingCTE
		WHERE Row_ID >= (@pgSize * @pgNbr) - (@pgSize - 1)
		AND Row_ID <= @pgSize * @pgNbr
	END
	GO

This stored procedure uses a Common Table Expression (CTE) to create row numbering. The variables include the sort column, first name, last name, page number, and page size. The where clause uses a formula based on the page size and page number to calculate which rows should be returned to the application. By submitting values such as @pgSize = 25 and @pgNbr = 1, we are telling the procedure to return the first page with 25 rows.

One important thing to note is the order by clause, which sorts the data based on the parameter passed in. The use of case expressions allows for dynamic sorting without the need for dynamic SQL, providing good performance and minimizing security risks.

It is worth mentioning that the above stored procedure can be further optimized by using control flow logic to introduce index seeks. However, this approach may result in a longer and more complex stored procedure.

Another option is to use parameterized dynamic SQL, which allows for greater flexibility and query plan reuse. By executing dynamic SQL using the sp_executesql system stored procedure, we can parameterize the statement and reduce the risk of injection attacks.

Here is an example of the stored procedure using parameterized dynamic SQL:

ALTER PROCEDURE usp_ContactPaging (
		@SortCol VARCHAR(25) = 'ContactId ASC',
		@FirstName VARCHAR(25) = NULL,
		@LastName VARCHAR(25) = NULL,
		@pgSize INT = 25,
		@pgNbr INT = 1
	)
	AS
	BEGIN
		DECLARE @SQL nvarchar(max), @params nvarchar(100)

		DECLARE @NbrPages INT

		IF @FirstName IS NULL AND @LastName IS NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts
		END

		IF @FirstName IS NOT NULL AND @LastName IS NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName
		END

		IF @FirstName IS NULL AND @LastName IS NOT NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [LastName] = @LastName
		END

		IF @FirstName IS NOT NULL AND @LastName IS NOT NULL
		BEGIN
			SELECT @NbrPages = CEILING(count(*) / (@pgSize * 1.0)) FROM Contacts WHERE [FirstName] = @FirstName AND [LastName] = @LastName
		END

		SET @params = N'@SIZE INT, @nbr INT, @Pages INT, @Sort VARCHAR(25)'
		SET @SQL = N'
			;WITH PagingCTE (Row_ID, ContactId, FirstName, LastName)
			AS
			(
				SELECT --YOU CAN REMOVE THE CASE AND USE THE SORT VARIABLE HERE BUT IT --INCREASES SUSCEPTIBILITY
					ROW_NUMBER() OVER (
						ORDER BY
							CASE WHEN @Sort=''FirstName DESC'' THEN FirstName END DESC,
							CASE WHEN @Sort=''FirstName ASC'' THEN FirstName END ASC,
							CASE WHEN @Sort=''LastName ASC'' THEN LastName END ASC,
							CASE WHEN @Sort=''LastName DESC'' THEN LastName END DESC,
							CASE WHEN @Sort=''ContactID ASC'' THEN ContactId END ASC,
							CASE WHEN @Sort=''ContactID DESC'' THEN ContactId END DESC
					) AS [Row_ID],
					ContactId,
					FirstName,
					LastName
				FROM Contacts '
				+ CASE
					WHEN @FirstName IS NOT NULL AND @LastName IS NULL THEN N'WHERE FirstName = ' + QUOTENAME(@FirstName, '''')
					WHEN @FirstName IS NULL AND @LastName IS NOT NULL THEN N'WHERE LastName = ' + QUOTENAME(@LastName, '''')
					WHEN @FirstName IS NOT NULL AND @LastName IS NOT NULL THEN N'WHERE FirstName = ' + QUOTENAME(@FirstName, '''') + ' AND ' + N'LastName = ' + QUOTENAME(@LastName, '''')
				END
				+ N'
			)
			SELECT
				Row_ID,
				ContactId,
				FirstName,
				LastName,
				@Nbr AS PageNumber,
				@Pages AS TotalNbrPages
			FROM PagingCTE
			WHERE Row_ID >= (@SIZE * @nbr) - (@SIZE -1)
			AND Row_ID <= @SIZE * @nbr'

		EXEC sp_executesql @SQL, @params, @SIZE = @pgSize, @nbr = @pgNbr, @Pages = @NbrPages, @Sort = @SortCol
	END
	GO

Parameterized dynamic SQL allows for greater flexibility and query plan reuse. By using sp_executesql to execute the dynamic SQL statement, we can pass parameters and reduce the risk of injection attacks.

Custom pagination in SQL Server can greatly improve user satisfaction, page load times, and reduce the amount of data returned to the application. By implementing custom pagination logic using Row_Number, developers can achieve better performance and scalability in their applications.