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