How to make pagination with MySql? Easy! Just use "select" and "limit" :

SELECT id, name FROM tbl ORDER BY name, id LIMIT 20, 10

This command returns 10 records starting from 20'th. It's very useful feature especially for web programming.
Few years ago I started to work with MSSql. And I was really disappointed: there is no such ability in MSSQL! So huge product doesn't have it!


Here is one solution, that I used:

SELECT TOP 30 id, name FROM tbl ORDER BY name, id
WHERE id NOT IN (SELECT TOP 20 id FROM tbl ORDER BY name, id)

It can looks terrible when you use lots of search and sort conditions.

Update [2008-09-02].
One more MSSQL solution. Using of ROW_NUMBER function in MSSQL 2005+:

WITH tmp_tbl AS
(
    SELECT id, name,
    ROW_NUMBER() OVER (ORDER BY name) AS 'RowNumber'
    FROM tbl 
) 
SELECT * 
FROM tmp_tbl
WHERE RowNumber BETWEEN 20 AND 30;

This case doesn't look simple. However it works faster.