...

View Full Version : SQL Server 2000 Row Limits



phantom007
07-02-2007, 11:50 AM
i All,

I have 20,000 records in my database

Is there any way i can run a sql query that can fetch the first 5000 row, then the next 5000 row and so on?

I do not want to fetch 20,000 rows at a time.


Plz help.


THanx

Daemonspyre
07-02-2007, 05:59 PM
You would do this by using WHERE statements in your SQL.

One could also use LIMITs, but it's not necessary.

Page 1:

SELECT yourFields FROM yourTable WHERE ID > 5000

Page 2:

SELECT yourFields FROM yourTable WHERE ID BETWEEN 5000 AND 10000

Page 3...4... and so on.

To do this programmatically, set variables (either by session or by Querystring) telling your SQL string which limits of the WHERE statement to include.

FOR EXAMPLE:


<%
if Request.Querystring("LL") = "" then
LL = "0" 'LL = lower limit
else
LL = request.querystring("LL")
end if
'
if Request.Querystring("UL") = "" then
UL = "5000"
else
UL = request.querystring("UL")
end if
'
'
rs.open "SELECT yourFields FROM yourTable WHERE ID BETWEEN " & LL & " AND " & UL & " ORDER BY ID ASC;", conn

%>

javabits
07-02-2007, 09:05 PM
Have you looked at using cursors. I believe they have been available since SQL Server 6.0

semper fi...

TheShaner
07-02-2007, 10:42 PM
One could also use LIMITs, but it's not necessary.
SQL Server does not utilize the LIMIT keyword. Other databases, like MySQL and PostGreSQL, do, however. If SQL Server did utilize the LIMIT keyword, that would definitely be the ideal way to paginate your results.

SELECT yourFields FROM yourTable WHERE ID > 5000
What if the OP isn't using an incremental integer ID field? Or records have been deleted and so only 4000 records exist between 1 and 5000? I have to disagree on using this method! Sorry, hehe.

Cancer10, if you are using SQL Server 2005 or above, you may follow this method:
http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx

This is another pagination solution, although a bit complex:
http://www.4guysfromrolla.com/webtech/042606-1.shtml

And last, but not least, there is the TOP keyword technique (implemented from 7.0 and on):

--Grabbing records 5001 - 10000
SELECT *
FROM
(
SELECT TOP 5000 field1, field2, etc.
FROM
(
SELECT TOP 10000 field1, field2, etc.
FROM table
ORDER BY field1 ASC
) AS tmptbl1
ORDER BY field1 DESC
) AS tmptbl2
ORDER BY field1 ASC
-Shane

Daemonspyre
07-02-2007, 10:56 PM
Shane --

You caught me -- I exclusively use MySQL, ergo, I was not familiar with the fact that MS SQL Server didn't use ANSI Standard SQL. (What?! MS not use standards??? No....)

My example was one of simplicity, and it's not always the best solution.

phantom007
07-03-2007, 05:00 AM
Shane --

You caught me -- I exclusively use MySQL, ergo, I was not familiar with the fact that MS SQL Server didn't use ANSI Standard SQL. (What?! MS not use standards??? No....)

My example was one of simplicity, and it's not always the best solution.

Hi

I do not want to sound like a nerd but what actually is ANSI Standard SQL?


Thanx

Daemonspyre
07-03-2007, 01:15 PM
ISO - International Standards Organization
ANSI - American National Standards Institute

ISO/ANSI Standards picked up SQL back in the mid-80's. Basically, it says that all RDBMS products should use the same language (SQL) so that they can safely interoperate and ease portability issues.

Of course, you will find that some RDBMS companies have different functions within their systems, but have the same overall basic features.

Here's a Wikipedia link for you to peruse at your leisure:

ISO/ANSI SQL (http://en.wikipedia.org/wiki/SQL)

NB - You are not a nerd nor a geek. Information is there to be learned. If you aren't learning something new every day, what's the point to life?

miranda
07-03-2007, 04:06 PM
Have you looked at using cursors. I believe they have been available since SQL Server 6.0

semper fi...

Cursors are not a good idea as they have a very large overhead involved in using them



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum