Developers had to go either 2 ways:
- Page records after retreiving from DB (using ASP.NET datagrid paging for example)
- Make a custom paging solution to page results in the server side.
The second option can be applied using many techniques, each has its pros and cons, a good discussion about these techniques is:
Paging of Large Resultsets in ASP.NET
In sql server 2005, things made easier using Common table expressions and the new ranking functions, especially: row_number()
Using row_number and Common table expression you can make an efficient SQL Server paging for resultsets.
For example, assume you want to return only the page #30 of the employees table sorted by name, and the page size is 10:
With EmployeesCTE
as
(
Select row_number() over (order by Name) as rownum,*
from Employees
)
Select * from EmployeesCTE
Where rownum between 291 and 300
Order by Name
The first part: (With EmployeesCTE..) creates a common table expression, which can be referenced in subsequent T-SQL statements and you can consider it as an (on the fly) view.
The row_number function returns the record number based on the order specified in the OVER clause.
The second part is the easy one, now you have a simple view (EmployeesCTE) that contains the
record number, you can easily get the range of records you need using:
Between x and y
.
3 comments:
It is easy to get a specified range of record by "between x and y". But how can I determine the max no of rownum ? If I put a number which is out of the range of records, no records will be show. Of course, you can get the recordcount by another query in advance. But is there any other way to do it?
You can select count(*) in the CTE. But I'm not sure about the impact on performance.
Some applications don't allow users to navigate directly to a specific page number, it forces them to go page by page and avoids this issue.
Try this http://developmentzone.blogspot.com/2008/09/paging-from-sql-server-table-records.html
Post a Comment