How to use LIMIT [X] OFFSET [Y] with SQL Server 20

问题:

Possible Duplicate:
Equivalent of LIMIT and OFFSET for SQL Server?

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 ?

回答1:

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.

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:

http://www.sqlservercentral.com/articles/T-SQL/66030/

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

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.

EDIT While some have demonstrated cases where Denali performs better on the first page (eg http://mssqltips.com/tip.asp?tip=2420), 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).