How to return a page of results from SQL?

by

On MS SQL Server 2005 and above, ROW_NUMBER() seems to work:

T-SQL: Paging with ROW_NUMBER()

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

More Related Contents:

  1. LINQ to Entities does not recognize the method
  2. Which method performs better: .Any() vs .Count() > 0?
  3. LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria
  4. Why is LINQ JOIN so much faster than linking with WHERE?
  5. Linq to Entities – SQL “IN” clause
  6. store image in database or in a system file? [closed]
  7. LINQ on the .NET 2.0 Runtime
  8. Wrap a delegate in an IEqualityComparer
  9. Bulk Insert to Oracle using .NET
  10. Linq int to string
  11. How can you do paging with NHibernate?
  12. .NET List Concat vs AddRange
  13. .NET LINQ query syntax vs method chain
  14. Does End Using close an open SQL Connection
  15. Does this code prevent SQL injection?
  16. Why use LINQ Join on a simple one-many relationship?
  17. Sanitize table/column name in Dynamic SQL in .NET? (Prevent SQL injection attacks)
  18. How can you name the Dataset’s Tables you return in a stored proc?
  19. Join and Include in Entity Framework
  20. Entity Framework 4 Single() vs First() vs FirstOrDefault()
  21. Why is some sql query much slower when used with SqlCommand?
  22. Why does IQueryable.All() return true on an empty collection?
  23. How to Convert LINQ Comprehension Query Syntax to Method Syntax using Lambda
  24. Is it possible to send a collection of ID’s as a ADO.NET SQL parameter?
  25. What can I do to resolve a “Row not found or changed” Exception in LINQ to SQL on a SQL Server Compact Edition Database?
  26. Why are some object properties UnaryExpression and others MemberExpression?
  27. How can I configure Entity Framework to automatically trim values retrieved for specific columns mapped to char(N) fields?
  28. How to perform merge sort using LINQ?
  29. Linq: What is the difference between Select and Where
  30. Regular expression to find all table names in a query

Leave a Comment