Query record numbering and/or Select Next 50 2

[Joiner]

Hi all.
Forgive me for beating a dead horse but after reading many archived posts on this subject I still need some help. I have a stored procedure that returns a huge recordset to the end user. I would like to split this and take the Top 50, Next 50, Next 50 and so on. I've reviewed several possibilities including:

1. Using a temp table with an identity field - dumping all of the records into a temp table would take forever.

2. Select Top 50 ID From Table Where ID not in (Select Top 0 (50, 100, 150 etc) ID From Table) - I don't know what the subqueried 'Top' value will be until the sp is run and I cannot pass a parameter to it.

3. Using a subquery like this -
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
In this case my original query involves far too many tables and joins to use this subquery.

Does anyone have any ideas? Am I missing something obvious?
Thanks for your help.

 

[tlbroadbent]

See the follwing articles:

Paging through Records using a Stored Procedure

http://www.4guysfromrolla.com/webtech/062899-1.shtml

Managing Result Set Paging

http://www.microsoft.com/sql/using/tips/administration/resultset.asp

Paging: Use ADO, getrows, or a Stored Procedure?

http://www.15seconds.com/issue/010308.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Joiner]

Thank you Terry. Those links were very helpful. I have one more question - if the TOP operator is used without an ORDER BY clause in a query like the one below , does it automatically use the first field to determine TOP number of records? Meaning, will the records always be sorted in ascending order of the first field?

Select TOP 50 ID, Firstname, Lastname
From Customers

Thanks again. Those articles were great.

 

[fluteplr]

No. If there is no order specified, you get pot luck. SQL will decide what order and it may not be the same from one time to the next.

General Rule:

IF you want the records in a specific order you must ALWAYS specify the order in the select statment.

If anyone doubts that check for various older threads referencing how MS 'broke' some ones code by changing the order in which an 'unordered' query was returned, when going from 6.5 to 7 or 7 to 2000 etc.

 

[Joiner]

Thanks for the quick and informative response!

 

[tanman1975]

if there's a clustered index on the table, the records should be returned in order of the index.