Was trying to use the new SQL2005 ROW_NUMBER() function to get “paged” results. There’s a bit of a trick to using it though. Only way we could get it to work was to wrap inside another sql clause.
E.g we have a table of Names, and we display them in groups of 10. So to get the 2nd page can do a query such as (of course you’d put this in an sp with params for the page size and number etc)
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY [Name]) as RowNumber, [Name] FROM Names) AS Names WHERE RowNumber BETWEEN 11 AND 20