Emulate MySQL LIMIT clause in Microsoft SQL Server 2000

Question

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

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

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