LIMIT and OFFSET in TSQL

Some of you who work with MYSQL databases for web development may know that there’s no easy way to do LIMIT and OFFSET in Microsoft’s SQL Server. This feature is critical for pagination used on a majority of the websites online, but the lack of support shows you which market segment Microsoft’s really after (enterprise, data warehouses, etc). After all, big companies have the big bucks…

Looking online, there seems to be one prevalent option to solve this issue:

SELECT TOP [MyLimit] *
FROM (SELECT TOP [MyTotal] * FROM [MyTable] ORDER BY [OrderColumn]) as T
ORDER BY [OrderColumn] DESC

While this does work, it has quite a few drawbacks:

1. Performance is slow.
2. Results returned is in descending order and is hard to manipulate.

The much better option to use is this:

WITH temp AS
(SELECT *, ROW_NUMBER() OVER (ORDER BY OrderColumn) AS rownum from MyTable)
SELECT * FROM temp WHERE rownum BETWEEN 0 AND 10

The advantages are:

1. Much better performance
2. Easier manipulation: this format is similar to the LIMIT and OFFSET in MySQL/PostgreSQL and also provides easier sorting.