Search code examples
sqlpaginationlimitsql-server-2000

Efficient Paging (Limit) Query in SQLServer 2000?

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?

Solution

  • 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. !