Equivalent of LIMIT and OFFSET for SQL Server?

This feature is now made easy in SQL Server 2012.This is working from SQL Server 2012 onwards.

Limit with offset to select 11 to 20 rows in SQL Server:

SELECT email FROM emailTable WHERE user_id=3ORDER BY IdOFFSET 10 ROWSFETCH NEXT 10 ROWS ONLY;

• ORDER BY: required
• OFFSET: optional number of skipped rows
• NEXT: required number of next rows

Reference: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql

The equivalent of LIMIT is SET ROWCOUNT, but if you want generic pagination it's better to write a query like this:

;WITH Results_CTE AS(    SELECT        Col1, Col2, ...,        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum    FROM Table    WHERE <whatever>)SELECT *FROM Results_CTEWHERE RowNum >= @OffsetAND RowNum < @Offset + @Limit

The advantage here is the parameterization of the offset and limit in case you decide to change your paging options (or allow the user to do so).

Note: the @Offset parameter should use one-based indexing for this rather than the normal zero-based indexing.

select top {LIMIT HERE} * from (      select *, ROW_NUMBER() over (order by {ORDER FIELD}) as r_n_n       from {YOUR TABLES} where {OTHER OPTIONAL FILTERS}) xx where r_n_n >={OFFSET HERE}

A note:This solution will only work in SQL Server 2005 or above, since this was when ROW_NUMBER() was implemented.