Following my post about Paging which was actually a comment on Dino Esposito's discussion on removing paging feature from ADO.NET, I'm starting series of posts on Paging datasource implemented via pure t-sql. Benjamin Crawford also requested this. Here is the first post of the series - Paging via RowCount. If your dream was to pass 1687250 rows through the wire to ADO.NET and then hoping that ADO.NET dataset/datareader would divide it properly by pages, think again about it a bit. I hope that the following piece of code is self-explanatory enough.
alter proc P_GET_PAGE
(
@p_page int,
@p_page_size int
)
as
begin
set nocount on
declare @p_total_rows_num int
declare @p_first_selecting_row_num int
declare @p_first_selecting_row_id int
select @p_total_rows_num = count(sales_id) from sales_big
select @p_first_selecting_row_num = (@p_page - 1) * @p_page_size + 1
if (@p_first_selecting_row_num <= @p_total_rows_num)
begin
set rowcount @p_first_selecting_row_num
select @p_first_selecting_row_id = sales_id
from sales_big
order by 1
set rowcount @p_page_size
select * from sales_big
where sales_id >= @p_first_selecting_row_id
order by 1
end
set nocount off
end
The example uses bigpubs2000 database, which is actually BIG PUBS database. 1687250 rows in sales_big table only. So lets test it.
.-------------------------------------------------------------------- Sample procedure showing how to page large data sets in server.-- This sample works only if sorting is not used.---- Based on Anatoly Lubarsky's sample code-- http://blogs.x2line.com/al
The sorting thing & paging itself can be easily realized via temp table. The idea is as follows.
1. create the temp table with unique_id (IDENTITY(1,1))
2. fill it with the data from your real table sorted by whatever you want
3. calculate the first & last records
