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

Question

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 ?

Purser

Answer 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 (also see this post on simulating keyset).

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 Paging – The Holy Grail

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 (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).

Underproduction