Equivalent of LIMIT and OFFSET for SQL Server?

Question

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=3
ORDER BY Id
OFFSET 10 ROWS
FETCH 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_CTE
WHERE RowNum >= @Offset
AND 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.

You can use ROW_NUMBER in a Common Table Expression to achieve this.

;WITH My_CTE AS
(
     SELECT
          col1,
          col2,
          ROW_NUMBER() OVER(ORDER BY col1) AS row_number
     FROM
          My_Table
     WHERE
          <<<whatever>>>
)
SELECT
     col1,
     col2
FROM
     My_CTE
WHERE
     row_number BETWEEN @start_row AND @end_row

Specifically for SQL-SERVER you can achieve that in many different ways.For given real example we took Customer table here.

Example 1: With "SET ROWCOUNT"

SET ROWCOUNT 10
SELECT CustomerID, CompanyName from Customers
ORDER BY CompanyName

To return all rows, set ROWCOUNT to 0

SET ROWCOUNT 0  
SELECT CustomerID, CompanyName from Customers
    ORDER BY CompanyName

Example 2: With "ROW_NUMBER and OVER"

With Cust AS
( SELECT CustomerID, CompanyName,
ROW_NUMBER() OVER (order by CompanyName) as RowNumber 
FROM Customers )
select *
from Cust
Where RowNumber Between 0 and 10

Example 3 : With "OFFSET and FETCH", But with this "ORDER BY" is mandatory

SELECT CustomerID, CompanyName FROM Customers
ORDER BY CompanyName
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

Hope this helps you.