Sumit Gupta

Pagination Query for SQL Server

In recent past, we got few problem when we need to do Pagination with SQL Server. I specially got frustrated when I saw that MySQL server has “limit” word that does pagination work in MySQL in best known manner for me, BUT microsoft didn’t consider that option yet. But today I found a method for SQL server that does the pagination in similar way as MySQL limit works. I am not very sure if it runs on SQL Server 2000 or earlier but it runs on SQL Server 2005. The magic word here is “ROW_NUMBER()” function that provides a Serial No. kind of Numbering to each record. So to do paging all you need to do is, make Query calling ROW_NUMBER() and you are done.

Here is sample of such query

SELECT * FROM
    (SELECT ROW_NUMBER()
        OVER (ORDER BY EmployeeName) AS Row,
        EmployeeId, EmployeeName, Salary
    FROM Employees) AS EMP
WHERE Row BETWEEN 2 AND 4

I took this sample and knowledge from a great resources for .NET developer. Website URL is http://www.openwinforms.com/row_number_to_sql_select.html

Hope you like the solution. Any alternative is welcome.

Sumit Gupta

,
,

4 responses to “Pagination Query for SQL Server”

  1. It will not work for versions of SQLServer prior to 2005 as these do not natively support a function such as row_number() (it has been introduced for SQLServer 2005 due to the lack of such a useful function). There are different solutions to cope with this issue for previous, ranging from stored procedures to tricky SQLs. You may google it.

  2. Yup, I am expecting that it won’t run there, and luckily I hardly need to run anything on SQL server 2000 (infact most of us) so it is good for pagination for now :).