Row Offset in SQL Server

I would avoid using SELECT *. Specify columns you actually want even though it may be all of them.

SQL Server 2005+

SELECT col1, col2 
FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

SQL Server 2000

Efficiently Paging Through Large Result Sets in SQL Server 2000

A More Efficient Method for Paging Through Large Result Sets

If you will be processing all pages in order then simply remembering the last key value seen on the previous page and using TOP (25) ... WHERE Key > @last_key ORDER BY Key can be the best performing method if suitable indexes exist to allow this to be seeked efficiently - or an API cursor if they don't.

For selecting an arbitary page the best solution for SQL Server 2005 - 2008 R2 is probably ROW_NUMBER and BETWEEN

For SQL Server 2012+ you can use the enhanced ORDER BY clause for this need.

SELECT  *
FROM     MyTable 
ORDER BY OrderingColumn ASC 
OFFSET  50 ROWS 
FETCH NEXT 25 ROWS ONLY 

Though it remains to be seen how well performing this option will be.