Efficient Paging (Limit) Query in SQLServer 2000?

sql sql-server-2000 paging limit
Loki picture Loki · Feb 2, 2009 · Viewed 7.5k times · Source

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?

Answer

Petar Petrov picture Petar Petrov · Feb 6, 2009

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

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How to get the first and last date of the current year?

Using SQL Server 2000, how can I get the first and last date of the current year? Expected Output: 01/01/2012 and 31/12/2012

Read More