What would be the most efficient way to do a paging query in SQLServer 2000?
Where a "paging query" would be the equivalent of using the LIMIT statement in MySQL.
EDIT: Could a stored procedure be more efficient than any set based query in that case?
Paging of Large Resultsets and the winner is using RowCount. Also there's a generalized version for more complex queries. But give credit to Jasmin Muharemovic :)
DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn
The article contains the entire source code.
Please read the "Update 2004-05-05" information. !