How many queries, percentage wise, would actually be ordered
by an identity column ... such that the noted technique would be valid?
Actually, you can make this technique value in all cases, and it's
actually the only way to get repeatable results back.
Say you're using the select from select from select from... approach
over a customer table, and you're sorted on lastname, firstname. It's
possible, even likely, that you've got a number of Mike Smiths in the
database. Your order by would be "order by lastname, firstname" yes? But
what order are the duplicate Mike Smiths going to show up in? Since you
didn't specify then the order is not defined, and there is _NO_
guarantee that they will come back in the same order next time, so the
Mike Smith with customer # 12345 may have been the 1001st row first time
and missed the 1-1000 page and been the 999th row the second time and
missed the 1001-2000 page. Therefore...
Make your order by "order by lastname, firstname, customer#" and then
have the called pass in the last customer # they received (@LastNumber).
Then you do this sort of thing:
select @lastname = LastName, @FirstName=FirstName from customer where
customer#=@LastNumber
select top 1000 * from customer
where
(lastname=@LastName and FirstName=@FirstName and
Customer#>@LastNumber)
or (lastName=@LastName and FirstName>@FirstName)
or (lastName>@LastName)
-Walden
--
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com
Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)
midrange.com
