July 13, 2002 at 6:33 pm
I'm new to SQL and I'm wondering: how does a search engine limit the number of results returned to a certain range (i.e., the 11th through 20th matches)? Surely it doesn't SELECT everything that matches; there could be potentially millions of matches.
July 13, 2002 at 7:18 pm
Typically there is a primary key on a table which makes rows unique. If the primary key is something like an integer, then it's really easy to return results from 11 to 20. Let's call this case A. Also, the TOP keyword in the SELECT statement can restrict the number of records returned based on a query. Let's call this case B.
Here's a query demonstrating case A:
SELECT OrderNumber, OrderName, OrderDate
FROM Orders
WHERE OrderNumber BETWEEN 11 AND 20
Here's a query demonstrating case B:
SELECT TOP 10 OrderNumber, OrderName, OrderDate
FROM Orders
WHERE OrderName = 'Matthews Consulting'
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 15, 2002 at 6:50 am
However going back to your statement and adding to what was stated. The query engine uses your conditions against the indexes on the database to meet you criteria. If like in the case where brian states you have a consistent situation interger column you can use as you start and end point, top, set rowcount, or a subquery getting counts of position based on a specific order to determine 11 thru 20. There are many ways. When you ask how the search engine limits it are you talking based on a query where you defined to find the records or using say ado and moving to a specific range. If the first then what brian stated and I added are the basics, if the later, then it depends on various settigns of the ado object. Cursor type, lock type, and location of cursor mainly. Depedning on what you are doing you may actually read all the records into memory to work with, or you may yes poisitions to read just what you need into memory until you reach a point you stop. ANd yes if you have millions of rows and read all into memory it may take a while.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply