Anatoly Lubarsky Logo
programming, design, integration, games, music

T-SQL Paging Series. RowCount

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.


exec P_GET_PAGE 3, 50

Update: idea by Alex Berendeyev


Related Posts:

Saturday, May 29, 2004 10:12 PM

Comments

# RE: T-SQL Paging Series. RowCount
Just a question about
select @p_first_selecting_row_num = (@p_page - 1) * @p_page_size + 1

Shouldn't it be
select @p_first_selecting_row_num = ((@p_page - 1) * @p_page_size) + 1

5/29/2004 11:51 PM by Luciano Evaristo Guerche

# re: T-SQL Paging Series. RowCount
It's facinating how much time folks spend on paging ;~}

5/30/2004 2:01 AM by <Alex/>

# re: T-SQL Paging Series. RowCount
<Alex/>: It's originally your example, changed :)

5/30/2004 2:13 AM by Anatoly Lubarsky

# re: T-SQL Paging Series. RowCount
I know, AL, I know ;~}

5/30/2004 2:19 AM by <Alex/>

# re: T-SQL Paging Series. RowCount
Do you have some url, so I will link to you from this post :)

5/30/2004 2:21 AM by Anatoly Lubarsky

# re: T-SQL Paging Series. RowCount
Unfortunately, no. No website, no blog... Too little time to maintain them.

5/30/2004 2:26 AM by <Alex/>

# re: T-SQL Paging Series. RowCount
I updated the post :)

5/30/2004 2:27 AM by Anatoly Lubarsky

# re: T-SQL Paging Series. RowCount
Wow, thanks!

5/30/2004 2:29 AM by <Alex/>

# about the "bigpubs2000 database"
Hello,
I apologize for the off-topic comment.
Can you tell me where can I get the bigpubs2000 database?

Regards,
Jorge Chandra

7/8/2004 12:36 AM by Jorge Chandra

# re: T-SQL Paging Series. RowCount
It was attached to one of the MSSQL2000 books, "MSSQL unleashed", if I'm not wrong here.

7/8/2004 12:56 AM by Anatoly Lubarsky

# Thanks!!
Thanks for the information!!

7/8/2004 3:15 AM by Jorge Chandra

# re: T-SQL Paging Series. RowCount
So how well does this query perform in your tests?

7/15/2004 1:07 AM by Senkwe

# re: T-SQL Paging Series. RowCount
It performs ok. The advantage of this query is that the performance remains constant and does not depend on parameter values.

7/15/2004 5:14 AM by Anatoly Lubarsky

# re: T-SQL Paging Series. RowCount
Good work! But it obviously fails if an ORDER BY is applied, cos' then all IDs will be shuffled!

7/25/2004 11:30 PM by Hiep Trinh

# Paging by T-SQL

9/2/2004 9:27 AM by a runner on Microsoft.NET

# Blog Paging via T-SQL Stored Proc

9/19/2004 1:30 PM by load of Tosh

# Example: Paging
.-------------------------------------------------------------------- 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

7/22/2005 12:43 AM by GPF

# re: T-SQL Paging Series. RowCount
Hello world! :)

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

SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)

4. select data from your temp table with limit conditions like:

SELECT
person_id,firstname,lastname
FROM
#TempTable
WHERE
unique_id > @FirstRec
AND unique_id < @LastRec

5. that's it.

12/14/2005 1:20 PM by Michael Yevdokimov

# MSSQL 2005: Paging with ROW_NUMBER is the fastest

3/24/2006 11:23 PM by Anatoly Lubarsky

# re: T-SQL Paging Series. RowCount
Hi Michael,

Your method is NOT good. It would take long time to create a such "Temp Table" if there are a large number of record in the original table.

The method in the post is good because it only uses PROJECTION operations in the SQL statements which just only cost a little time.

Thanks.

5/14/2007 9:53 AM by Jack

# Articles about @@rowcount volume 2 &laquo; Article Directory
Articles about @@rowcount volume 2 &laquo; Article Directory

6/13/2010 9:39 AM by Pingback/TrackBack

# Articles about @@rowcount volume 2 &laquo; Article Directory
Articles about @@rowcount volume 2 &laquo; Article Directory

6/13/2010 11:07 AM by Pingback/TrackBack

# Articles about @@rowcount volume 2 &laquo; Article Directory
Articles about @@rowcount volume 2 &laquo; Article Directory

6/13/2010 12:35 PM by Pingback/TrackBack

# Articles about @@rowcount volume 2 &laquo; Article Directory
Articles about @@rowcount volume 2 &laquo; Article Directory

6/13/2010 7:09 PM by Pingback/TrackBack

# T-SQL: Paging with ROW_NUMBER() | mytechconnect
T-SQL: Paging with ROW_NUMBER() | mytechconnect

3/22/2013 5:36 AM by Pingback/TrackBack

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(04) December, 2008
(04) November, 2008
(05) October, 2008
(04) September, 2008
(05) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(04) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(04) October, 2007
(05) September, 2007
(12) August, 2007
(11) July, 2007
(14) June, 2007
(13) May, 2007
(13) April, 2007
(10) March, 2007
(11) February, 2007
(14) January, 2007
(14) December, 2006
(12) November, 2006
(08) October, 2006
(09) September, 2006
(06) August, 2006
(08) July, 2006
(10) June, 2006
(09) May, 2006
(22) April, 2006
(25) March, 2006
(12) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(16) October, 2005
(16) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(12) May, 2005
(12) April, 2005
(20) March, 2005
(11) February, 2005
(12) January, 2005
(18) December, 2004
(13) November, 2004
(12) October, 2004
(14) September, 2004
(09) August, 2004
(23) July, 2004
(19) June, 2004
(29) May, 2004
(19) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs