此文章发布于 66 个月前，部分信息可能已经过时，请自行斟酌确认。

sql server 2012 新增 order by 子句的 offset fetch 语法，可以实现分页，简单测试了一下，我的结论是语法写起来比 row_number 要简洁，但效率上不像网上其它人的文章所说的要好一丢丢，我测试的是差一丢丢，并且越往后面的页数取数效率越差，可能我测试的场景不全面。但我选择放弃了。还是用 row_number。

语法

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

官方文档

点此查看

示例

select 
  c.WarehouseGuid,c.UseOrgGuid,o.Name_CN as Lydwmc,c.BillDate,c.BillNO,c.RowNO,c.RowGuid,c.SaleStreamNO,
  c.MaterialGuid,d.Code,c.LocationNO,c.BatchNO,c.PackNO,c.FabricLevelGuid,c.BaseQuantity,
  c.AuxQuantity,b.CreateUserName,c.Note
from STK_MaterialPick_YnDyeFab b 
    left join STK_MaterialPickDetail_YnDyeFab c on c.BillTypeCode = b.BillTypeCode and c.BillDate = b.BillDate and c.BillNO = b.BillNO 
    left join BAS_Material_YnDyeFab d on d.Guid=c.MaterialGuid
    left join BAS_OrgFrame o on o.Guid=c.UseOrgGuid
where 1=1
  and c.BillDate>'2017/08/01'
  and b.CancelUser is null 
  and c.BaseQuantity>=0
order by b.BillNO desc
offset (50-1)*15 row fetch next 15 rows only

查询计划

3 个图片为执行了 3 次对比，上面是 offset fetch 语法，下面是 row_number