SQL Server 2008 paging methods?

by

The following T-SQL stored procedure is a very efficient implementation of paging. THE SQL optimiser can find the first ID very fast. Combine this with the use of ROWCOUNT, and you have an approach that is both CPU-efficient and read-efficient. For a table with a large number of rows, it certainly beats any approach that I’ve seen using a temporary table or table variable.

NB: I’m using a sequential identity column in this example, but the code works on any column suitable for page sorting. Also, sequence breaks in the column being used don’t affect the result as the code selects a number of rows rather than a column value.

EDIT: If you’re sorting on a column with potentially non-unique values (eg LastName), then add a second column to the Order By clause to make the sort values unique again.

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS

DECLARE @FirstId int, @FirstRow int

SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow

-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.

SELECT   @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SET ROWCOUNT @PageSize

SELECT   *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

SET ROWCOUNT 0
GO

More Related Contents:

  1. What is the best way to paginate results in SQL Server
  2. How do I obtain a Query Execution Plan in SQL Server?
  3. Count(*) vs Count(1) – SQL Server
  4. When should I use CROSS APPLY over INNER JOIN?
  5. Equivalent of LIMIT and OFFSET for SQL Server?
  6. LIMIT 10..20 in SQL Server
  7. INNER JOIN vs LEFT JOIN performance in SQL Server
  8. SQL JOIN vs IN performance?
  9. Is a view faster than a simple query?
  10. Multiple INSERT statements vs. single INSERT with multiple VALUES
  11. efficient way to implement paging
  12. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  13. Implement paging (skip / take) functionality with this query
  14. Does Foreign Key improve query performance?
  15. Counting DISTINCT over multiple columns
  16. Fastest way to determine if record exists
  17. SQL count(*) performance
  18. Why is a UDF so much slower than a subquery?
  19. Favourite performance tuning tricks [closed]
  20. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  21. Can an INNER JOIN offer better performance than EXISTS
  22. How to check which locks are held on a table
  23. SQL Server SELECT LAST N Rows
  24. When using GETDATE() in many places, is it better to use a variable?
  25. T-SQL Skip Take Stored Procedure
  26. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  27. Can anyone help me to get the query to retrieve data like below [closed]
  28. Optimal way to concatenate/aggregate strings
  29. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  30. SQL – Call Stored Procedure for each record

Leave a Comment