Yes, this is a very cool feature that has been missing! Nice post.

Am I doing something wrong, but I can not get this to work....I get:

Msg 207, Level 16, State 1, Line 3
Invalid column name 'RowNumber'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'RowNumber'.

Thanks

Thanks to Ben Reichelt over at CodeBetter.com blogs for pointing out the new Row_Number() function in...

You don't have to specify tht ORDER BY FirstName at the end of the query. It's important that if you write paging code like this, you formulate your complete ORDER BY clause in the OVER clause for ROW_NUMBER().

Why is this important? Because the ORDER BY in the select will be executed AFTER the row_number() function has been applied. This means that the ROW_NUMBER() column doesn't have to be 1, 2, 3, 4, 5 etc.

You should therefore use these rules of thumb:
- place the SELECT's ORDER BY clause in the ROW_NUMBER's OVER clause
- instead add an ORDER BY clause to the SELECT to sort the ROW_NUMBER column ASC. This is necessary because by default a SELECT isn't guaranteed to be sequential, it can be that the rows aren't in the order in which you would expect. This is by definition of SELECT.

So then, use a WITH statement to construct a CTE:
WITH __actualSet
(
SELECT C.*, ROW_NUMBER() OVER (ORDER BY CompanyName ASC) AS RowNumber FROM Customers C
ORDER BY RowNumber ASC
) SELECT * FROM __actualSet WHERE RowNumber BETWEEN 1 AND 10

Nice feature, but it still seems more complicated than it needs to be. LIMIT works beautifully as is. I guess there is some other crazy use for the resulting Row Number column that I'm not considering?...