Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

Efficient paging of recordsets: SQL Server 2000

Here's the most efficient way that I can think of  to return rows @a through @b from a table or query, when each row is ordered and can be uniquely identified by a set of columns. 

In this example, we have a table called “Data”, and we wish to sort by TransDate.  The primary key of the table is “ID” (yep, an identity !) so we include that as the secondary sort to ensure all rows can be uniquely identified by the sort columns. 

The plan: set the rowcount to the starting row number (in this case, @a), and get the value of the “starting points” for each sort column.  Then, set the rowcount to the number of rows to return (@b-@a), set up a  WHERE clause so that we start at the starting point, and then return the results.

This avoids temp tables and always processes as few rows as possible -- at most, @b rows.  So it will get slower as you go.  But, it works pretty well so give it a shot.  If your ordering needs to be dynamically assigned, then this technique may not do much for you but it may help give you some ideas.

Update #1 (3/22/04) : I have slightly edited the code to fix the "first row" error, as specified in the comments.

Update #2 (4/8/07): If you are using SQL Server 2005, it is much easier ... see this.


-- for each column in your sort, you need a variable to hold
-- the "starting values". In our case, we need two:

declare @startingDate datetime;

declare @startingID int;

 

-- again, we want to returns results from row @a to row @b:

declare @a int;

declare @b int;

 

set @a = 200 -- start at row 200

set @b = 250 -- end at row 250

 

-- get the starting date and starting ID to return results:

set rowcount @a

select @StartingDate = TransDate, @startingID = ID

from Data

order by TransDate ASC,ID ASC

 

-- find out how many rows to return, and set the rowcount:

set @b = 1 + @b - @a

set rowcount @b

 

-- now return the results:

select * from Data

where

TransDate > @StartingDate OR

(TransDate = @StartingDate AND ID >= @StartingID)

order by TransDate ASC, ID ASC

 

-- clean up:

set rowcount 0

 


 
The WHERE clause in the final SQL statement is the key, and it is good to be familiar with this type of clause even if you don't use this exact paging technique.  Look at it closely, and you will see it expresses logic to return rows within a certain range when that range is defined by more than 1 column.

For example, if you have a column called “YearMonth” with values in a YYYY-MM format, you can easily say:

WHERE YearMonth >= '2003-04'

But if you have two separate columns, one for Month and one for Year, you would express that same WHERE clause this way:

WHERE Year > 2003 OR (Year = 2003 AND Month >= 04)

Print | posted on Monday, December 22, 2003 12:31 PM | Filed Under [ T-SQL Paging Data Code Library - SQL ]

Feedback

Gravatar

# re: Efficient paging of recordsets with T-SQL

Good job! I posted my own approach (linked to my name here) in response. It is more database independent and reusable, but moves the record-skipping logic to .NET (there are advantages and disadvantages).
12/22/2003 3:48 PM | Richard Tallent
Gravatar

# re: Efficient paging of recordsets with T-SQL

what about order desc and using top 1??
12/23/2003 6:53 PM | ValterBorges
Gravatar

# re: Efficient paging of recordsets with T-SQL

This code has a bug in it that means it cannot return the very first row. The example starts from row 200 but actually returns the next row, ie 201 (since the first select gets the item at row 200, and the second selects gets the next value > than this value).

This means that if you want row 1, you should specify row 0, ie to get the next row. However if you do that, the first select will do a rowcount 0 and set the starting row to the last row of the select. (And if you specify row 1, it will return from row 2.)

My fix is to surround the second select with "if @@rowcount >= @nStartRow" so it only does it if the first select returned more rows that the row we want to start from. And change the where clause to "TransDate = @StartingDate AND ID >= @StartingID".

It now includes the start row, so the example would return from 200 rather than 201. And so it allows row 1 to be returned.
12/29/2003 5:32 AM | TheKode
Gravatar

# re: Efficient paging of recordsets with T-SQL

Thanks for the fix! sorry I missed that.

Valter -- not sure what you mean -- can you elaborate ?
12/30/2003 2:07 PM | Jeff
Gravatar

# Efficient and DYNAMIC Server-Side Paging with T-SQL

3/22/2004 10:29 PM | Jeff's Blog
Gravatar

# re: Efficient paging of recordsets with T-SQL

FYI -- I have altered the code slightly to handle the "skipped first row" issue. It should work quite well now. Check out my dynamic version of this technique in my latest blog ....
3/24/2004 3:22 PM | Jeff
Gravatar

# Paging a recordset - SQL Server side.

3/27/2004 4:14 PM | Dim Blog As New ThoughtStream(Me
Gravatar

# Paging Dr. Recordset, Dr. Recordset, line 1.

3/29/2004 9:00 PM | Dim Blog As New ThoughtStream(Me
Gravatar

# Paging a recordset - SQL Server side.

3/29/2004 9:02 PM | Dim Blog As New ThoughtStream(Me
Gravatar

# Creating Effecient Paging with T-SQL and the IDENTITY function

Creating Effecient Paging with T-SQL and the
IDENTITY function

Now we all had the problem of creating a grid

with next and back buttons , as it retreieves

all the data from the server and filters it

on the client site .That is a poor technique

if you have a huge table ,few users will hang

your machine and result in memory consumption


SQL server lacks a way to limit the number

of rows returned to the client in ranges ,and

you have to write a very complex query to

achieve this result ,like getting rows from 5

to 10 ,and you will depend on a primary key

for sure .Lets first discuss how MySQL and

Oracle solves this problem ...

Mysql has a very pretty code to solve this

problem you can say :

SELECT * FROM foo LIMIT 5,10

this will retrieve the rows starting row

number 5 till row number 15 .Very pretty code

cause u just drop this { LIMIT N,M } at the

end of your most complex query and you are

done .


Oracle has a similar approach ,it has a

psuedoColumn always availiable to you when

you issue a query , this column have a name

of ROWNUM .
if you do something like this
SELECT ROWNUM ,* FROM foo you will get

ROWNUM COl1 Col2
1 data data
2 data data
3 data data

Now to page just say WHERE ROWNUM BETWEEN n

AND M
Pretty clean code just like the mysql code

Now back to my only love SQLserver ,which is

the best engine i worked on despite this

issue ,there is no direct way to return a

range ,you can only return TOP ,but no range

Here is a little piece of code that you can

compile as a stored procedure

SET ROWCOUNT 10 -- set this to your desired

page size ,this is for optimization ,the

between clause will be enough

select IDENTITY(int, 1,1) as 'ROWNUM' ,*

into #temptable from foo
SELECT * from #temptable will yield this

ROWNUM COl1 Col2
1 data data
2 data data
3 data data

which is similar to the ORACLE approach

,and the code will be even compatible with

ORACLE as i can say
SELECT * FROM #temptable where ROWNUM between
N and M

your code will be easy to read and understand
Amr Salah
6/7/2004 8:04 AM | PRINCE OF EGYPT
Gravatar

# re: Efficient paging of recordsets with T-SQL

This thread is old, but interesting, so I thought I'd take a poke...

If you know beforehand that your always going to need rows 200 to 250, this would work:

SELECT *
FROM (SELECT TOP 50 *
FROM (SELECT TOP 250 *
FROM Data
ORDER BY TransDate ASC
, ID ASC) a
ORDER BY TransDate DESC
, ID DESC) b
ORDER BY TransDate ASC
, ID ASC


If the range is to be specified dynamically, then you can make it into dynamic SQL...

DECLARE @sql VARCHAR (8000)
, @a INT
, @b INT

SELECT @a = 200
, @b = 250

SELECT @sql = 'SELECT *'
+ ' FROM (SELECT TOP ' + CONVERT (VARCHAR, @a) + ' *'
+ ' FROM (SELECT TOP ' + CONVERT (VARCHAR, @b) + ' *'
+ ' FROM Data'
+ ' ORDER BY TransDate ASC'
+ ', ID ASC) a'
+ ' ORDER BY TransDate DESC'
+ ', ID DESC) b'
+ ' ORDER BY TransDate ASC'
+ ', ID ASC'
EXEC (@sql)


Better? Worse? I don't know if this approach is more or less efficient. But I think it meets the specification and it's arguably a simpler approach.
6/8/2004 10:53 AM | Lee Dise
Gravatar

# re: Efficient paging of recordsets with T-SQL

The last solution (reversing the results) doesn't work on the last page. It will always return the number of rows in a page.
7/22/2004 2:31 PM | Kevin
Gravatar

# Paging by T-SQL

9/2/2004 1:27 AM | a runner on Microsoft.NET
Gravatar

# Efficient paging of recordsets with T-SQL

Efficient paging of recordsets with T-SQL
9/22/2004 9:06 AM | Mahalakshmi Natarajan
Gravatar

# re: Efficient paging of recordsets with T-SQL

There's something I don't like in this 'rowcount' setting. If any error occurs between the 'set rowcount x' statement and 'set rowcount 0' statement, many queries will return wrong results.

The rowcount property stays modified for the 'link' between the client and the server and if something wrong occurs -> sh.t happens.

This might also lead to security issues (which I already had to handle because of that rowcount conflict).

So, when using that property, be really sure your request cannot fail.
9/22/2004 1:03 PM | antoine
Gravatar

# re: Efficient paging of recordsets with T-SQL

I want to ask how can I get the record of one page in a eperate array. and so on..
Actually I m using a function which returns records in an array.
9/13/2005 12:58 AM | Aatif Chaudhry
Gravatar

# re: Efficient paging of recordsets with T-SQL

Any way to get a record count out of this?
9/14/2005 8:25 PM | Dave
Gravatar

# re: Efficient paging of recordsets with T-SQL

Perfect! Other solutions I have found only work if you wanted to select groups of records using a unique identifier only.

The query makes my asp.net custom paging really efficient, it brings back the bare minumum of data required, whilst not not requiring cursors,temp tables, or even Sql2005.
10/26/2005 7:02 PM | Jason Epstein
Gravatar

# re: Efficient paging of recordsets with T-SQL

Great! Thank you . I was using a paging solution using temp tables and it reduced the time with at least 25% (recorded with a select on a table with 230.000+ records with joins and left joins with 5-6 other tables). Can't wait to see what happens with a table with over 2 million records and 5-6 joined tables.

Best regards,
Lucian Chiriac
12/28/2005 11:59 AM | Lucian Chiriac
Gravatar

# re: Efficient paging of recordsets with T-SQL

I enjoyed! Very good solution!!!
2/22/2006 11:37 AM | Nataly
Gravatar

# re: Efficient paging of recordsets with T-SQL

Nice job! Thanks!
4/14/2006 7:09 AM | Jenny
Gravatar

# re: Efficient paging of recordsets with T-SQL

hi "AM Lee Dise"
what u do is nice
but when ig i have 5 million or more rows!
then what will i do?
bring 5 million rows and then take 50 of them that slow :(
5/11/2006 7:02 AM | peleg
Gravatar

# re: Efficient paging of recordsets with T-SQL

PLEASE can anyone tell me how to use NEXT and Back buttons in the query results of large data in SQL 2005
5/29/2006 3:35 PM | Uncle Sam
Gravatar

# re: Efficient paging of recordsets with T-SQL

this simply won't work:
set rowcount @a

select @StartingDate = TransDate, @startingID = ID

from Data

order by TransDate ASC,ID ASC

does not matter what you assign to the rowcount, the select will always return the transdate and id of the first row in the table.
8/28/2006 1:18 PM | annon
Gravatar

# re: Efficient paging of recordsets with T-SQL

annon -- Did you try it?
8/28/2006 1:55 PM | Jeff
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

This is a good article. I've also read a few on the topic at the following locations:

http://aspnet.4guysfromrolla.com/articles/031506-1.aspx
http://www.4guysfromrolla.com/webtech/042606-1.shtml

The only thing I'm trying to figure out is how to use this to both sort my result set DESC AND implement search functionality..

p.ProcessID >= @FirstID AND
p.IsDeleted = 0 AND
p.ProcessID = ISNULL(@ProcessID, p.ProcessID) AND
i.VendorID = ISNULL(@VendorID, i.VendorID) AND
etc.

jB
5/21/2007 2:53 PM | John Bonds
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

JB -- keep your sorting and your filtering separate; you can use a derived table to help you.

i.e.,

select x.*
from
( your filtered select here .... )x
where
... your paging condition here ....
order by
....

5/21/2007 2:58 PM | Jeff
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

Thanks, I tryed this aproach, but the grid view custom paging also requires the number of rows and I couldn't find a way to get that because of rowcount and making the count before was even more expensive than the temp table.
By the way, I think you avoid using the temp table 'cause the table you use has a consecutive id, my table does'nt have that consecutive Id, so I think it's impossible to avoid the temp table unless we had something like Oracle's rownumber.
Any way, we all should be in SQL Svr 2005 by now. I f we are into this yet, is becaue our IT departments havn't decided to change yet. :(
Bye.
6/8/2007 5:32 PM | Ernesto
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

does not matter what you assign to the rowcount, the select will always return the transdate and id of the first row in the table.
9/7/2008 12:27 PM | rüya tabiri
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

does not matter what you assign to the rowcount, the select will always return the transdate and id of the first row in the table.

Thank
5/5/2009 6:44 PM | Foxchat Sohbet
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

thank you
5/22/2009 9:24 AM | lig tv izle
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

Thank You Admin
5/22/2009 8:30 PM | Edencity Chat
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

thank you askim
6/29/2009 10:18 AM | liseli kizlar
Gravatar

# re: Efficient paging of recordsets: SQL Server 2000

chat rooms turkish thank you very much..
10/9/2009 8:38 PM | chat
Gravatar

# konya chat

KONYA SOHBET
11/22/2009 11:56 AM | konya chat
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET