Sorting and Paging Recordsets in SQL Server

Here's a stored procedure that I use for sorting and paging large recordsets in SQL Server, as opposed to using the more common and terribly inefficient entire dataset approach in .NET.  It certainly doesn't matter much in the little classroom examples of a few hundred, or even thousands, of records, but working with larger recordsets with datasets is just bad.  This is even more true with ASP.NET, since the entire dataset is usually saved in viewstate, and since the web server is doing the processing for many users.  This stored procedure is certainly not the only way to do sorting and paging in SQL Server, but it is probably the most efficient, although proper indexing is still required to avoid table scans.  Note that this does not use temporary tables, which are convenient but not as optimal -- this also means you could modify this to be dynamic SQL for Access or other databases!  The parameters are the name of the table, the name of the primary key (necessary for the little bit of optimization included), the name of the sort field (or sort fields, with or without ASC/DESC), and the page size (number of records).  It also allows optional parameters for the page index (defaults to 1st page) and a query filter (defaults to null) so you can sort and page through filtered records only!  Note that it returns two recordsets -- the first is the results you expect, and the second recordset is a scalar with the number of total pages so you can better define the GUI pager for a grid.  This is relatively easy to integrate with the ASP.NET datagrid if you use custom paging and sorting, and it will minimize both your load on the server and the amount of data sent to the client's browser!  By the way, this code is just modified from some I found on the net, and there are certainly some minor optimizations that can be done, like using different sql for the first page.

CREATE PROCEDURE GetSortedPage(
  @TableName VARCHAR(50),
  @PrimaryKey VARCHAR(25),
  @SortField VARCHAR(100),
  @PageSize INT,
  @PageIndex INT = 1,
  @QueryFilter VARCHAR(100) = NULL
) AS
SET NOCOUNT ON

DECLARE @SizeString AS VARCHAR(5)
DECLARE @PrevString AS VARCHAR(5)

SET @SizeString = CONVERT(VARCHAR, @PageSize)
SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1))

IF @QueryFilter IS NULL OR @QueryFilter = ''
BEGIN

  EXEC(
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ')
    ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
  )
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName)

END
ELSE
BEGIN

  EXEC(
  'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN
    (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN
      (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ')
    ORDER BY ' + @SortField + ')
  ORDER BY ' + @SortField
  )
  EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter)

END

RETURN 0
GO

16 Comments

  • Just taking a quick look, you could change the "SELECT Count(*)" to "SELECT Count(@PrimaryKey)" and that should give you a speed boost. Reducing the number of columns in the select statement always speeds things up, even for aggregate operations like Count. Very interesting though; I will have to see how well it works for complex queries.

  • Very interesting! Right now I'm using a solution based on creating a temporary table in the stored procedure for selecting rows from a table containing over a million rows. I will test to implement this solution and see if it boosts performance, which would be very welcome :)



    In my project I will have to make an inner join to retrieve User names from another table, but since I only have to do this on the outermost SELECT-statement (after TableName), and I have a good indexed structure, that shouldn't have a big impact on performance.



    Any thoughts?

  • what's the asp supposed to look like?

  • I was wondering if you had an example of using this code with an asp.net datagrid, complete with paging and sorting. email: mwpat@rogers.com

  • I use it internally at work on a project, but that's not an example I can share. I also use this, but wrapped up in my WilsonORMapper, on my site, but there I use repeaters since I don't like datagrids. There are lots of other examples that show you the asp.net code that you want, so I haven't bothered to recreate one yet again -- the only thing I'm pointing out is how to set up the stored procedure in a better way.

  • quite helpful - babu

  • Sorry, I'm new to sql. Why the extra "SELECT * FROM ~ IN ~" statement?



    Why not just do a "SELECT TOP ' + @SizeString + ' * FROM ..." and make the second select the top select (if you see what I mean). Wouldn't this have the same affect?



  • Great stuff!

    I spent last couple of hours looking for something similar and I could see this proc working very well in my project.



    Thanks!

  • Has anybody tried this for tables with a few million rows yet? Thanks.

  • I believe I read somewhere the sp_executesql will compile the dynamic query and create an execution plan for it. This may speed this up a bit if the same exact query is executed.

  • Excuse me but i have never used a stored procedure before, and although your code works great (thanks a lot by the way :)) i want to ask how can i get the PageCount value from the second table generated by ur stored procedure? Thank you very much in advance :)

  • Assuming you're using .NET and a DataReader then the NextResult method is what you want. If you are using a .NET DataSet then you will look at the second table, i.e. .Tables[1].

  • I'm using sql server, and the NextResult method doesn't work, is there another way, i mean using sql server? thanks

  • This is a great SP and just what I was looking for. I know this is a dumb question...I don't understand how to get the recordset pagecount in ASP. I see the pagecount being returned when I execute the SP in a new query...

  • I found that this did not work when performing a descending sort on the second page of a multipage recordset. However adding an additional parameter for sort order and only calling it on the outermost sql statement fixed the issue.

  • This was written long before Sql 2005 was around with the RowNumber function. My ORMapper does support using the RowNumber function with Sql 2005 for optimal paging.

Comments have been disabled for this content.