SQL Paging

Paging in SQL is a technique that allows you to pull chunks of rows from a dataset at a time. They would be called pages. I like to use this behind partial page updates. Specifically a grid or table uses “infinite scrolling”. As the user scrolls to the bottom of a table, a javascript event fires off a request to get the next page of rows. When the server returns them, they get appended to the bottom of the table and the scrolling continues.

This technique shows its value when the number of records is so numerous that the browser’s task of rendering the data causes poor performance. Google’s image search and Facebook is a good example of infinite scrolling. If the browser were to be tasked with loading 100% of the search result content, the user experience would suffer with very poor performance.

While there have been some workaround techniques for this feature, it was made officially available in SQL Server 2012.

A Large DataSet

/****** Object:  Table [dbo].[Person]    Script Date: 8/26/2020 12:26:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Person](
	[PersonID] [uniqueidentifier] NOT NULL,
	[FirstName] [nvarchar](50) NOT NULL,
	[LastName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
	[PersonID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Person] ADD  CONSTRAINT [DF_Person_PersonID]  DEFAULT (newid()) FOR [PersonID]
GO

This table is populated with 500 records each having a FirstName and LastName. I want to create a Stored Procedure that allows me to pass in a PageNumber and PageSize parameter. Inside the procedure these parameter values will be used to calculate the subset of records to return.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	GO
	DECLARE 
	   @PageNumber INT = 1,
	   @PageSize INT = 25

	EXEC PersonPaging @PageNumber, @PageSize
		
*/
ALTER PROCEDURE PersonPaging
(
   @PageNumber INT,
   @PageSize INT
)
AS
BEGIN
    SET NOCOUNT ON

    SELECT 
		PersonID
		, FirstName
		, LastName
	FROM Person
	ORDER BY LastName
	OFFSET ((@PageNumber - 1) * @PageSize) ROWS
	FETCH NEXT @PageSize ROWS ONLY

END
GO

According to the example above, this will return the 1st page of 25 records from my Person table that contains 500. The magic happens in lines 29-30.

NOTE: Page numbering begins with 1. Paging is not a zero-based index.

Let’s Take This One Step Further…

If we think about the UI that might display this data, you might commonly see a label on the table that reads “Showing x of xxx Records”. We can add a bit to this stored procedure that will return the total count of rows in the whole dataset while only providing the rows for the requested page. Have a look…

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
	GO
	DECLARE 
	   @PageNumber INT = 1,
	   @PageSize INT = 25,
	   @TotalRowCount INT

	EXEC PersonPaging @PageNumber, @PageSize, @TotalRowCount OUTPUT
		
*/
ALTER PROCEDURE PersonPaging
(
   @PageNumber INT,
   @PageSize INT,
   @TotalRowCount INT OUTPUT
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

	DECLARE @tblResults TABLE (
		PersonID UNIQUEIDENTIFIER,
		TotalRowCount INT,
		FirstName NVARCHAR(50),
		LastName NVARCHAR(50)
	)

	INSERT INTO @tblResults
    SELECT 
		PersonID
		, COUNT(*) OVER() AS TotalRowCount
		, FirstName
		, LastName
	FROM Person
	ORDER BY LastName
	OFFSET ((@PageNumber - 1) * @PageSize) ROWS
	FETCH NEXT @PageSize ROWS ONLY

	SELECT TOP 1 @TotalRowCount = TotalRowCount
	FROM @tblResults

	SELECT PersonID, FirstName, LastName
	FROM @tblResults

END
GO

The modifications to the original stored procedures include:

1. An additional parameter declared as an OUTPUT
2. A table variable declared with the same schema as the desired output
3. An INSERT statement on top of the SELECT statement
4. An extra column in the SELECT statement: COUNT(*) OVER() AS TotalRowCount
5. A quick SELECT statement to assign the row count to the OUTPUT parameter
6. A final SELECT statement to return the data from the table variable