如何在 SQL Server 2005 中使用 LIMIT [X] OFFSET [Y] [英] How to use LIMIT [X] OFFSET [Y] with SQL Server 2005

问题描述

可能的重复:
SQL Server 的 LIMIT 和 OFFSET 的等价物?

如何在 SQL Server 2005 中使用 LIMIT [X] OFFSET [Y]，我看到 mysql、oracle、sqllite、postgre 都有，微软没有意识到我们需要它?

How to use LIMIT [X] OFFSET [Y] with SQL Server 2005, i see mysql, oracle, sqllite, postgre have it, microsoft dont realize we need it ?

推荐答案

Microsoft(您可能会说，进行更改)选择实施 ANSI 标准，而不是提出自己的专有关键字，如 LIMIT.在下一版本的 SQL Server(代号为Denali")中，您将能够使用 OFFSET/FETCH(另见 这篇关于模拟键集的帖子).

Microsoft (for a change, you might say) chose to implement the ANSI standard instead of coming up with their own proprietary keyword like LIMIT. In the next version of SQL Server (code-named "Denali") you will be able to use OFFSET / FETCH (also see this post on simulating keyset).

与此同时，您将需要对 SQL Server 使用不同的方法(或者，如果您认为这比额外键入更容易，则切换平台).一篇关于现有版本中几种分页方法的好文章在这里:

In the meantime, you will need to use different methods with SQL Server (or switch platforms if you think that is easier than extra typing). A good article about a few methods for paging in existing versions is here:

• SQL Server 2005 分页——圣杯

请务必点击加入讨论"；阅读 60 多条后续评论，这些评论讨论了所提供解决方案的各个方面.

Be sure to click on "Join the Discussion" to read over the 60+ follow-up comments that discuss various aspects of the solutions provided.

请注意，出于性能原因未实现 OFFSET/FETCH；只为生产力.换句话说，OFFSET/FETCH 的性能与当今通常使用的 ROW_NUMBER() 解决方案大致相同.

Note that OFFSET / FETCH is not implemented for performance reasons; only for productivity. In other words, OFFSET / FETCH will perform about the same as the ROW_NUMBER() solutions typically in use today.

编辑 虽然有些人已经证明了 Denali 在第一页上表现更好的情况(例如 这个)，这不是目的，我不相信当你遍历表并得到到后面的页面(特别是在大表上).

EDIT While some have demonstrated cases where Denali performs better on the first page (e.g. this one), this is not the intention, and I'm not convinced the delta remains true as you traverse the table and get to the latter pages (particularly on large tables).

这篇关于如何在 SQL Server 2005 中使用 LIMIT [X] OFFSET [Y]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！