Emulate MySQL LIMIT clause in Microsoft SQL Server 2000

Question

When I worked on the Zend Framework's database component, we tried to abstract the functionality of the LIMIT clause supported by MySQL, PostgreSQL, and SQLite. That is, creating a query could be done this way:

$select = $db->select();
$select->from('mytable');
$select->order('somecolumn');
$select->limit(10, 20);

When the database supports LIMIT, this produces an SQL query like the following:

SELECT * FROM mytable ORDER BY somecolumn LIMIT 10, 20

This was more complex for brands of database that don't support LIMIT (that clause is not part of the standard SQL language, by the way). If you can generate row numbers, make the whole query a derived table, and in the outer query use BETWEEN. This was the solution for Oracle and IBM DB2. Microsoft SQL Server 2005 has a similar row-number function, so one can write the query this way:

SELECT z2.*
FROM (
    SELECT ROW_NUMBER OVER(ORDER BY id) AS zend_db_rownum, z1.*
    FROM ( ...original SQL query... ) z1
) z2
WHERE z2.zend_db_rownum BETWEEN @offset+1 AND @offset+@count;

However, Microsoft SQL Server 2000 doesn't have the ROW_NUMBER() function.

So my question is, can you come up with a way to emulate the LIMIT functionality in Microsoft SQL Server 2000, solely using SQL? Without using cursors or T-SQL or a stored procedure. It has to support both arguments for LIMIT, both count and offset. Solutions using a temporary table are also not acceptable.

Edit:

The most common solution for MS SQL Server 2000 seems to be like the one below, for example to get rows 50 through 75:

SELECT TOP 25 *
FROM ( 
  SELECT TOP 75 *
  FROM   table 
  ORDER BY BY field ASC
) a 
ORDER BY field DESC;

However, this doesn't work if the total result set is, say 60 rows. The inner query returns 60 rows because that's in the top 75. Then the outer query returns rows 35-60, which doesn't fit in the desired "page" of 50-75. Basically, this solution works unless you need the last "page" of a result set that doesn't happen to be a multiple of the page size.

Edit:

Another solution works better, but only if you can assume the result set includes a column that is unique:

SELECT TOP n *
FROM tablename
WHERE key NOT IN (
    SELECT TOP x key
    FROM tablename
    ORDER BY key
);

Conclusion:

No general-purpose solution seems to exist for emulating LIMIT in MS SQL Server 2000. A good solution exists if you can use the ROW_NUMBER() function in MS SQL Server 2005.

Shorthand

Answer 1

SELECT TOP n *
FROM tablename
WHERE key NOT IN (
    SELECT TOP x key
    FROM tablename
    ORDER BY key
    DESC
);

Drinking

Yes, this is close, but it only works when there's a unique key in the interim query result. How would you do it in a GROUP BY query or for a query that joins several tables? – Shorthand

Answer 2

Here is another solution which only works in Sql Server 2005 and newer because it uses the except statement. But I share it anyway. If you want to get the records 50 - 75 write:

select * from (
    SELECT top 75 COL1, COL2
    FROM MYTABLE order by COL3
) as foo
except
select * from (
    SELECT top 50 COL1, COL2
    FROM MYTABLE order by COL3
) as bar

Creamy

Thanks, that looks like it would work too in Microsoft SQL Server 2005. It still leaves us with no good solution for Microsoft SQL Server 2000. But I guess since it's 2009, it's finally reasonable to drop support for 2000 anyway. – Shorthand

Answer 3

When you need LIMIT only, ms sql has the equivalent TOP keyword, so that is clear. When you need LIMIT with OFFSET, you can try some hacks like previously described, but they all add some overhead, i.e. for ordering one way and then the other, or the expencive NOT IN operation. I think all those cascades are not needed. The cleanest solution in my oppinion would be just use TOP without offset on the SQL side, and then seek to the required starting record with the appropriate client method, like mssql_data_seek in php. While this isn't a pure SQL solution, I think it is the best one because it doesn't add any overhead (the skipped-over records will not be transferred on the network when you seek past them, if that is what worries you).

Aggrade

I agree with your recommendation when designing an application. In my case, I was designing a database-access framework that was supposed to have a consistent API but produce different SQL as needed for different brands of database. The solution had to be in the SQL preparation, not in the fetching. – Shorthand

Nice idea, thank you very much. Overall I think that your solution is the more viable. – Agaric

The problem with this solution is not so evident when you're in the first few pages. What if you need to go to the last page of 10,000 pages and there is 8k or more per row? I am not sure how mssql_data_seek could possibly prevent SQL Server from selecting all of those rows (even if they ultimately aren't rendered by the client). If this is the solution you plan to use, I highly recommend testing all the edge- and worst-case scenarios. – Koumis

Answer 4

I would try to implement this in my ORM as it is pretty simple there. If it really needs to be in SQL Server then I would look at the code generated by linq to sql for the following linq to sql statement and go from there. The MSFT engineer who implemented that code was part of the SQL team for many years and knew what he was doing.

var result = myDataContext.mytable.Skip(pageIndex * pageSize).Take(pageSize)

Haakon

msdn.microsoft.com/en-us/library/bb386988.aspx says: "Translation is different for SQL Server 2000 and SQL Server 2005. If you plan to use Skip(Of TSource) with a query of any complexity, use SQL Server 2005." – Shorthand

My point is that there are lots of good solutions for this problem if the prerequisite is to run MS SQL Server 2005. But few of them work in the general case on MS SQL Server 2000. Admittedly, it's now 2011, and it's increasingly hard to justify running MS SQL Server 2000 as time marches on. Yet some people still do! :( – Shorthand