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.
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.
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
.
http://tuvian.wordpress.com/2011/05/24/pagination-stored-procedure-in-sql/
It was very helpful.