Categories
.NET SQL

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.