Select next 100 rows
-
Hi,
I thought this will be really simple. I want to display the data in batch of 100s. I can select the first 100 using SELECT TOP 100. Now I don't know how to retrieve the next 100 records, from 101 to 200. I remember using LIMIT keyword in mySQL for somthing like this. But I couldn't figure out how to do this in TSQL in a single sql query. Is it possible to do? Is there anything like a unique row number that we can use along with BETWEEN? Iam using SQL Server 2000
Thanks.
-
you may be talking about...
SET ROWCOUNT 100 ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
if you data have unique constrain, then identity (or similar concept) may help you.
create table with identity column and key field of your data.
populate it with the key field.
join two table using identity field as batch break.
-
You could simply select the top 100 for the first query, then for the second 100 select the top 100 where the key column is > the last key column from the previous query. Make sense?
-
Thanks guys.
-
Senthil,
I'll probably get a real blast of hooey
for using Dynamic SQL but don't much give a rat's patooti 
because it runs in 13 milliseconds for the first page and only takes a max of 477 milliseconds to pull up the last page (page 10300). The table I use it on, Customer, has 103 Million rows and 78 columns of data. The Primary Key is CustID (INT) and is not clustered. We're using the Enterprise Edition of SQL Server 2000 with SP-3a (haven't tested SP-4 to see if it breaks anything, yet).With that in mind, here's a snippet of the code I use... I added some code to measure the actual speed of execution from Query Analyzer just so you could see what the time claims are based on.
--===== Setup to test speed without the overhead of SET STATISTICS ON
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
--===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @SQL VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 10000
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @SQL =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' *
FROM dbo.Customer WITH (NOLOCK)
WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID
FROM dbo.Customer
ORDER BY CustID)
ORDER BY CustID
) d
WHERE c.CustID = d.CustID
'
--===== Produce the page
EXEC (@SQL)
--===== Display the total time of the query with milliseconds
PRINT CONVERT(VARCHAR(12),GETDATE()-@StartTime,114)
Rumor has it that SQL Server 2005 will allow variables for the TOP function which would eliminate the need for the Dynamic SQL.
Of course, it's absolutely ridiculous to me that anyone would actually want access to such a table by display-pages (who in their right might is going to page through all 10300 pages
?) but wanted to show you the code and to let you know that there's no excuse for a paging return that takes more than a second or so.I used a similar technique when the customer said they wanted to enter a CustID and show the 20 customers entered before and after that customer. I guess I can see the use in that
Hope this helps...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Great solution.
Just a question: why a join and not an IN clause in the WHERE statement of the top SELECT?
It should save I/O time not having to pull from the DB all the fields of the customer table (alias d) that are not projected to the final output.
Regards
Giovanni
-
Giovanni... thanks for the questions.
The * selection in the top select of "d" was a nasty oversite
... it was a leftover from testing and should have been changed to just CustID. Thanks for catching that.The Join between the derived table "d" and the alias "c" could be done as an "IN" but I've not seen any advantage. performance or otherwise, to doing that. It was more out of habit... positive logic = joins, negative logic = not in if possible. Either way would work just fine...
The NOT IN does occassionally show a performance advantage over an outer join with null detection.
I'll do a retest with the * changed to CustID and see what the timing differences are.
Thanks again for the sharp eyes.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Well, I retuested with the correction Giovanni noticed (see last posting) and was a bit surprised... the change did nothing to the low end (1st page still returns in 13 milliseconds) and the high end (page 10300) improved a bit with a max at 453 milliseconds and an average of 420 milliseconds. Still, that's a 5% increase in speed when comparing the maxes to it's worth while.
Changing the * in the very outer select to just CustID made no difference in speed.
Here's the code for those interested...
--===== Setup to test speed without the overhead of SET STATISTICS ON
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
--===== Declare the local variables
DECLARE @PageSize INT --How many rows to appear per page
DECLARE @PageNum INT --What page number to appear
DECLARE @Skip INT --Working variable for where to start for page
DECLARE @SQL VARCHAR(8000) --Holds dynamic SQL
--===== Set the local variables for pagesize and page
-- PageSize and PageNum would be parameters in a stored proc
SET @PageSize = 100
SET @PageNum = 10300
SET @Skip = @PageSize*@PageNum
--===== Create the dynamic SQL statement that will produce the page
SET @SQL =
'
SELECT c.*
FROM dbo.Customer c,
(
SELECT TOP '+STR(@PageSize)+ ' CustID
FROM dbo.Customer WITH (NOLOCK)
WHERE CustID NOT IN (SELECT TOP '+STR(@Skip)+' CustID
FROM dbo.Customer
ORDER BY CustID)
ORDER BY CustID
) d
WHERE c.CustID = d.CustID
'
--===== Produce the page
EXEC (@SQL)
--===== Display the total time of the query with milliseconds
PRINT CONVERT(VARCHAR(12),GETDATE()-@StartTime,114)
Again, thanks for the sharp eyes, Giovanni.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SELECT C.*
FROM dbo.Customer c
INNER JOIN (SELECT c2.CustID, (SELECT COUNT(*)
FROM dbo.Customer C1
WHERE C1.CustID <=C2.CustID) as Row_Numb
FROM dbo.Customer c2 ) C0 on C0.CustID =C.CustID
WHERE Row_Numb between ...whatever
_____________
Code for TallyGenerator -
Serqiy,
That might work fine on small tables but I stopped the run after 37 minutes just trying to return the first page on my large table... here's the code...
SELECT C.*
FROM dbo.Customer c
INNER JOIN (SELECT c2.CustID, (SELECT COUNT(*)
FROM dbo.Customer C1
WHERE C1.CustID <=C2.CustID) as Row_Numb
FROM dbo.Customer c2 ) C0 on C0.CustID =C.CustID
WHERE Row_Numb between 1 and 100
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply