对我来说，一起使用OFFSET和FETCH很慢，所以我使用了TOP和OFFSET的组合，就像这样(这更快):

SELECT TOP 20 * FROM (SELECT columname1, columname2 FROM tablename
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

注意:如果在同一个查询中同时使用TOP和OFFSET:

SELECT TOP 20 columname1, columname2 FROM tablename
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS

然后你会得到一个错误，所以为了同时使用TOP和OFFSET，你需要用子查询来分离它。

如果你需要使用SELECT DISTINCT，那么查询如下:

SELECT TOP 20 FROM (SELECT DISTINCT columname1, columname2
    WHERE <conditions...> ORDER BY columname1 OFFSET 100 ROWS) aliasname

注意:使用SELECT ROW_NUMBER和DISTINCT不适合我。

我能做到的最接近

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

我猜类似于select * from [db].[dbo]。[table] LIMIT 0,10

另一个例子:

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;

您可以在公共表表达式中使用ROW_NUMBER来实现这一点。

;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

在Aaronaught的解决方案的基础上增加了一点变化，我通常对页码(@PageNum)和页面大小(@PageSize)进行参数化。这样，每个页面点击事件只发送请求的页码和可配置的页面大小:

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

这里有人在sql 2011中讲述了这个功能，令人遗憾的是，他们选择了一个有点不同的关键字“OFFSET / FETCH”，但它不是标准的，然后ok。