CODEWITHSUNDEEP
sql-serverperformancepaginationcompatibilitysql-server-2000
Vilx-
Vilx-

Reputation: 106920

How to efficiently limit returned rows in SQL Server 2000 (return rows from X to Y)?

I'm working on a system that has to be MS SQL Server 2000-compatible (yes, sucks, but tell that to our clients who are still using it). Anyways, I've come up against this roadblock of a situation...

There's this table that can have a lot of records in it (products table, really). One of our clients even has near to 1M records. I'm building a web shop, and need to display record from that table. Naturally, I should provide paging in the web, so I need an option to select only records from X to Y.

But how to do this in a way, that would be both efficient, and work even in SQL Server 2000?

Upvotes: 1

Views: 5935

Answers (4)

Amjad Abu Saa
Amjad Abu Saa

Reputation: 1664

I found this answer while searching for a similar solution:

SELECT
    product_name,
    list_price
FROM
    production.products
ORDER BY
    list_price,
    product_name 
OFFSET 10 ROWS 
FETCH NEXT 10 ROWS ONLY;

The full explanation is available here.

Upvotes: 0

SQLMason
SQLMason

Reputation: 3275

SWeko has a good answer and it was my first thought. If you didn't want to have the subquery, you could:

SET ROWCOUNT @Y

SELECT *
FROM products p1    
WHERE p1.productID > @X
ORDER BY p1.productID

SET ROWCOUNT 0

Although you'll have to remember @X which may not be an integer or an identity.

Upvotes: 2

SWeko
SWeko

Reputation: 30902

The only semi-workable solution for large table on SQL Server 2000 I know of is the pattern

SELECT TOP 100 *
  FROM products p1
  WHERE p1.productID not in (SELECT TOP 200 productID FROM products p2)

This will display rows 201-300.

If ordering and filtering is required (and it usually is) you need to make sure that the order and filter is the same in both query and subquery (otherwise, the results are a bit funky), like

SELECT TOP 100 *
  FROM products p1
  WHERE p1.productID not in (SELECT TOP 200 productID 
                               FROM products p2 
                               WHERE p2.productGroup = 2
                               ORDER BY p2.productName)
    AND p1.productGroup = 2
  ORDER BY p1.productName

(this returns the 201-300 product where productGroup is 2 ordered by name).

The downside to this approach is that SQL Server 2000 does not support the TOP @variable syntax, so the 100 and 200 in the above query cannot be easily parametricized. To generate this query you would either have to use an SP that will use dynamic SQL, or compile it on the client application, and then sent it to the database as-is.

In an unrelated rant, I always wondered why this is not supported on the database level, for example something like

SELECT ROW 201-300 *
  FROM products
  WHERE....

as this scenario appears over and over again.

Upvotes: 4

Dewfy
Dewfy

Reputation: 23624

We have used following technique:

SELECT TOP @top * FROM ...

Where @top is equal to (Y). So only top rows are selected by SQL server, to get expected page we have used simple fetching without data extraction (until X)

Upvotes: -1

Related Questions