Equivalent of LIMIT and OFFSET for SQL Server?

In PostgreSQL there is the `Limit` and `Offset` keywords which will allow very easy pagination of result sets.

What is the equivalent syntax for SQL Server?

In SQL server you would use TOP together with ROW_NUMBER()

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

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.

Another sample :

declare @limit int
declare @offset int
set @offset = 2;
set @limit = 20;
declare @count int
declare @idxini int
declare @idxfim int
select @idxfim = @offset * @limit
select @idxini = @idxfim - (@limit-1);
WITH paging AS
(
SELECT
ROW_NUMBER() OVER (order by object_id) AS rowid, *
FROM
sys.objects
)
select *
from
(select COUNT(1) as rowqtd from paging) qtd,
paging
where
rowid between @idxini and @idxfim
order by
rowid;

There is [here][1] someone telling about this feature in sql 2011, its sad they choose a little different keyword "OFFSET / FETCH" but its not standart then ok.


[1]:

[To see links please register here]

Adding a slight variation on Aaronaught's solution, I typically parametrize page number (@PageNum) and page size (@PageSize). This way each page click event just sends in the requested page number along with a configurable page size:

begin
with My_CTE as
(
SELECT col1,
ROW_NUMBER() OVER(ORDER BY col1) AS row_number
FROM
My_Table
WHERE
<<<whatever>>>
)
select * from My_CTE
WHERE RowNum BETWEEN (@PageNum - 1) * (@PageSize + 1)
AND @PageNum * @PageSize

end

The closest I could make is

select * FROM( SELECT *, ROW_NUMBER() over (ORDER BY ID ) as ct from [db].[dbo].[table] ) sub where ct > fromNumber and ct <= toNumber

Which I guess similar to `select * from [db].[dbo].[table] LIMIT 0, 10`

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.

select top (@TakeCount) * --FETCH NEXT
from(
Select ROW_NUMBER() OVER (order by StartDate) AS rowid,*
From YourTable
)A
where Rowid>@SkipCount --OFFSET